-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Arun Raj.C
Message 1 in Discussion
Here
are the seven monitoring reports. Hope you find them a useful addition to your
Transact-SQL toolkit!<o:p></o:p>
<![if !supportLists]>1. <![endif]>Failed jobs report. This query lists
the name of all
jobs that failed in their last attempt:<o:p></o:p>
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0<o:p></o:p>
<![if !supportLists]>2. <![endif]>Free space by drive. This is an
undocumented extended
stored procedure call that you can use to show free space by drive, for the SQL
Server instance:<o:p></o:p>
EXEC master..xp_fixeddrives<o:p></o:p>
<![if !supportLists]>3. <![endif]>Disabled jobs. ever have a
critical job that someone decided to disable? Catch it with this
script!<o:p></o:p>
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY
name<o:p></o:p>
<![if !supportLists]>4. <![endif]>Running jobs. Need to know what jobs
are still
running? Ever come into work on Monday morning to find several critical jobs
having �piled up�, running way to long, or hung up? This query
lists those running queries (whether normally scheduled or not). This procedure
call is good for making sure your Distribution or Merge agent job is still
running too. I make a call to sp_get_composite_job_info
(loading in a bunch of NULLS, and a �1� to indicate running jobs):<o:p></o:p>
msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL,
NULL, 1, NULL, NULL<o:p></o:p>
<![if !supportLists]>5. <![endif]>Server role members. Check which logins
are a member of server roles. Have co-workers who like to
slip in new sysadmin users? This will help you find
rogue server role assignments.<o:p></o:p>
SELECT 'ServerRole' = A.name, 'MemberName' =<o:p></o:p>
B.name FROM master.dbo.spt_values A, master.dbo.sysxlogins B WHERE A.low =
0 AND A.type = 'SRV' AND B.srvid IS NULL<o:p></o:p>
AND A.number & B.xstatus = A.number<o:p></o:p>
<![if !supportLists]>6. <![endif]>Last backup date. This query shows the
very last time
your databases were backed up.<o:p></o:p>
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day,
GetDate(), <o:p></o:p> MAX(Backup_finish_date)))), 'NEVER') as
DaysSinceLastBackup,<o:p></o:p> ISNULL(Convert(char(10),
MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate<o:p></o:p>
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
<o:p></o:p> ON A.database_name = B.name AND A.type = 'D' GROUP BY
B.Name ORDER BY B.name<o:p></o:p>
<![if !supportLists]>7. <![endif]>SQL Log. Reading the SQL Log using
Transact-SQL. This query shows the entries from the latest SQL log (the one
currently being updated). You can modify this script according to what
information you find important. As you can see, I like to filter out various
key works and entries that are part of non-critical SQL Server messages. This
script uses the undocumented extended stored procedure
xp_readerrorlog:<o:p></o:p>
CREATE TABLE #Errors (vchMessage varchar(255), ID
int)<o:p></o:p> CREATE INDEX idx_msg ON #Errors(ID,
vchMessage)<o:p></o:p> INSERT #Errors EXEC
xp_readerrorlog<o:p></o:p> SELECT vchMessage FROM #Errors WHERE
vchMessage NOT LIKE '%Log backed up%' AND<o:p></o:p> vchMessage
NOT LIKE '%.TRN%' AND vchMessage NOT LIKE '%Database backed up%' AND<o:p></o:p>
vchMessage NOT LIKE '%.BAK%' AND vchMessage NOT LIKE '%Run the
RECONFIGURE%' AND<o:p></o:p> vchMessage NOT LIKE '%Copyright
(c)%' ORDER BY ID DROP TABLE #Errors<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Warm
Wishes<o:p></o:p>
Arun
Raj.C<o:p></o:p>
http://spaces.msn.com/members/arunrajc<o:p></o:p>
<o:p> </o:p>
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
