Kunal Ranpura
- Aug 13, 2019
- 4 min read

5 - Scripts I use every single day

1). Script to see what is running in your SQL Server.

select * from sys.sysprocesses where status = 'suspended' and spid >10 and hostname <> 'MonitoringServer' order by blocked desc

SELECT session_id, plan_handle FROM sys.dm_exec_requests where status ='suspended' or status = 'runnable' and session_id >10

select * from sys.dm_os_wait_stats where wait_type not in

(select wait_type

from sys.dm_os_waiting_tasks wt

join sys.dm_exec_sessions s on s.session_id = wt.session_id

where is_user_process = 0)

order by wait_time_ms desc

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

BlkBy VARCHAR(255),DBName VARCHAR(255),

Command VARCHAR(255),CPUTime INT,

DiskIO INT,LastBatch VARCHAR(255),

ProgramName VARCHAR(255),SPID2 INT,

REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT *

FROM #sp_who2

-- Add any filtering of the results here :

WHERE DBName <> 'master' and Status = 'runnable' and HostName <> 'MonitoringServer'

-- Add any sorting of the results here :

ORDER BY CPUTime desc

DROP TABLE #sp_who2

Here 'MonitoringServer' is server name from where we are running SQL Monitoring. We are using Spotlight on SQL Server.

Output of the Query:

2). Find Lead blocker: Here you will see how to identify lead blocker from nested blocking chain.

---Lead Blocker

SELECT

spid

,sp.STATUS

,loginame = SUBSTRING(loginame, 1, 12)

,hostname = SUBSTRING(hostname, 1, 12)

,blk = CONVERT(CHAR(3), blocked)

,open_tran

,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)

,cmd

,waittype

,waittime

,last_batch

,SQLStatement =

SUBSTRING

(

qt.text,

er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

ELSE er.statement_end_offset

END - er.statement_start_offset)/2

)

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

3). Script to verify your recent database backups

SELECT sdb.name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime

FROM sys.databases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

where bus.type='D'

GROUP BY sdb.name

4). Script to verify if there any SQL Agent job failures:

Use msdb

select distinct j.Name as "Job Name", --j.job_id,

case j.enabled

when 1 then 'Enable'

when 0 then 'Disable'

end as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)] ,

case jh.run_status

when 0 then 'Failed'

when 1 then 'Successful'

when 2 then 'Retry'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as Job_Execution_Status

from sysJobHistory jh, sysJobs j

where j.job_id = jh.job_id and jh.run_date =

(select max(hi.run_date) from sysJobHistory hi where jh.job_id = hi.job_id )-- to get latest date

5). Script to Check SQL Server Errorlog. Verify if there are any errors in SQL Server Errorlog.

print ' '

print '==================='

print '== SQL Errorlogs =='

print '==================='

print ' '

declare

@FirstLog smallint,

@LastLog smallint,

@BufferRecords smallint,

@SQL varchar(2000),

@Output varchar(2000)

select

@FirstLog = 0, --> Defaults to current logfile

@LastLog = 0, --> Defaults to logfile immediately preceding current

@BufferRecords = 4 --> Used to give a frame of reference to the error message

---------------------------------------------------------------------------------------------------

-- Date Created: July 11, 2005

-- Description: This procedure combines and parses the SQL Error Logs. Entries of special

-- interest are marked which allows the operator to quickly scan the output for

-- errors. Entries of interest are removed from the output. Entries before and

-- after the errors are included to give a frame of reference.

---------------------------------------------------------------------------------------------------

-- Date Revised: December 7, 2006

-- Reason: I converted this procedure to support SQL Server 2005

---------------------------------------------------------------------------------------------------

set nocount on

declare @count smallint,

@alert char(5)

select @count = 0,

@SQL = '',

@output = '',

@alert = '---->'

---------------------------------------------------------------------

-- Validate input parameters --

---------------------------------------------------------------------

IF (@FirstLog > @LastLog)

BEGIN

select @lastLog = @FirstLog + 1

END

---------------------------------------------------------------------

-- M A I N P R O C E S S I N G --

---------------------------------------------------------------------

-- --

-- Create work tables --

-- --

-- Import SQL Error Logs --

-- --

-- Remove unwanted entries --

-- --

-- Mark items of interest --

-- --

-- Generate report --

-- --

---------------------------------------------------------------------

-- Create work tables --

---------------------------------------------------------------------

--IF (object_id('tempdb..#ERRORLOG') IS NOT NULL)

-- drop table #ERRORLOG

CREATE TABLE #ERRORLOG

(

LogID int identity primary key clustered,

LogDate datetime NULL,

ProcessInfo varchar(12) NULL,

Alert char(5) default ' ',

LogEntry varchar(900) NULL,

Row smallint NULL

)

-- Add indexes

create index [IX_ERRORLOG_Alert] on dbo.[#ERRORLOG](Alert) with fillfactor = 98 on [PRIMARY]

create index [IX_ERRORLOG_LogEntry] on dbo.[#ERRORLOG](LogEntry) with fillfactor = 98 on [PRIMARY]

---------------------------------------------------------------------

-- Import SQL Error Logs --

---------------------------------------------------------------------

select @count = @FirstLog

WHILE (@Count <= @LastLog)

BEGIN

insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')

insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')

select @output = '-- Processing: ERRORLOG' + case(@count) when 0 then ' ' else '.' + convert(char(3), @count) end + ' --'

insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',@output)

insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')

insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')

select @SQL = 'exec master..sp_readerrorlog ' + convert(varchar(3), @count)

insert into #errorlog (LogDate, ProcessInfo, LogEntry)

execute (@SQL)

select @count = @count + 1

END

---------------------------------------------------------------------

-- Remove unwanted entries --

---------------------------------------------------------------------

delete

from #ERRORLOG

where LogEntry like '%Bypassing recovery for database%'

or LogEntry like '%Setting database option ANSI_WARNINGS%'

or LogEntry like '%Log backed up with following information:%'

or LogEntry like '%Login succeeded for user%'

or LogEntry like '%Database differential changes%'

or LogEntry like '%The certificate was successfully loaded%'

or LogEntry like '%this is an informational message only%'

or LogEntry like '%logging sql server messages in file%'

or LogEntry like '%Recovery complete.%'

or LogEntry like '%Starting up database ''ERROR_LOGGING''%'

or LogEntry like '%Database backed up: Database: %'

or LogEntry like '%Log backed up: Database: %'

or LogEntry like '%allocate%'

---------------------------------------------------------------------

-- Mark items of interest --

---------------------------------------------------------------------

update #ERRORLOG

set Alert = @alert

where (LogEntry like '%err%'

or LogEntry like '%exception%'

or LogEntry like '%violation%'

or LogEntry like '%warn%'

or LogEntry like '%kill%'

or LogEntry like '%dead%'

or LogEntry like '%encounter%'

or LogEntry like '%cannot%'

or LogEntry like '%could%'

or LogEntry like '%fail%'

or LogEntry like '%full%'

or LogEntry like '%not%'

or LogEntry like '%terminate%'

or LogEntry like '%bypass%'

or LogEntry like '%recover%'

or LogEntry like '%roll%'

or LogEntry like '%upgrade%'

or LogEntry like '%victim%'

or LogEntry like '%stop%'

or LogEntry like '%shut%'

or LogEntry like '%timed out%'

or LogEntry like '%truncate%'

or LogEntry like '%terminat%')

and (ProcessInfo <> ' ' or ProcessInfo IS NULL)

---------------------------------------------------------------------

-- Generate report --

---------------------------------------------------------------------

BEGIN

-- Show entries of interest + buffer records

select A.LogID,

'Date' = isnull(convert(varchar(19), A.LogDate, 120), ''),

A.ProcessInfo,

A.Alert,

'Descripton' = left(A.logEntry, 250)

from #ERRORLOG A

join (select LogID from #ERRORLOG where Alert = @alert) B on (A.LogID >= (B.LogID - @BufferRecords))

and (A.LogID <= (B.LogID + @BufferRecords))

-- Show informational records

union

select A.LogID,

'Date' = isnull(convert(varchar(19), A.LogDate, 120), ''),

A.ProcessInfo,

A.Alert,

'Descripton' = left(A.logEntry, 250)

from #ERRORLOG A

where ProcessInfo = ''

order by A.LogID

END

Drop TABLE #ERRORLOG

5 - Scripts I use every single day

Comments