What is SPID in SQL Server

1.Failed jobs report. This query lists the name of all jobs that failed in their last attempt:

SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

2.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:

EXEC master..xp_fixeddrives

3.Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!

SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name


4.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):

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

5.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.

SELECT 'ServerRole' = A.name, 'MemberName' =
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
AND A.number & B.xstatus = A.number

6.Last backup date. This query shows the very last time your databases were backed up.

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name


7.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:

CREATE TABLE #Errors (vchMessage varchar(255), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)
INSERT #Errors EXEC xp_readerrorlog
SELECT vchMessage FROM #Errors WHERE vchMessage NOT LIKE '%Log backed up%' AND
vchMessage NOT LIKE '%.TRN%' AND vchMessage NOT LIKE '%Database backed up%' AND
vchMessage NOT LIKE '%.BAK%' AND vchMessage NOT LIKE '%Run the RECONFIGURE%' AND
vchMessage NOT LIKE '%Copyright (c)%' ORDER BY ID DROP TABLE #Errors