<![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>
