SYSTEMS +: SQL SERVER Monitoring Script

Problem

As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like:

What was SQL Server doing last night when the end users were experiencing slow application response times?
How much time the ABC job taken for execution?
Why the A job get failed?
Are the SQL services running?
What is the OLAP DB size?
Is the backup plan executed properly and what the size of the BKP files.

The answers to such questions are never easy to identify. So how can I figure this out?

Solution

The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time and delivers it on email with following information.

Here is the query:

CREATE PROC ServerInfo

SET NOCOUNT ON

IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name='##sqlservice')

DROP TABLE ##sqlservice

CREATE TABLE ##sqlservice (details VARCHAR(100))

IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name='##agentservice')

DROP TABLE ##agentservice

CREATE TABLE ##agentservice (details VARCHAR(100))

WAITFOR DELAY '00:02'

DECLARE @sname VARCHAR(100), @starttime VARCHAR(30)

DECLARE @authmode VARCHAR(25), @subject VARCHAR(250)

DECLARE @insname VARCHAR(50),@agentname VARCHAR(50)

DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100)

DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000)

DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000) ,@tableHTMLVARCHAR(8000)

DECLARE @dbcount int

select @dbcount=count (*) FROM sys.databases WHERE state_desc <>'ONLINE'

SELECT @sname = @@SERVERNAME

SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1then 'Windows' else 'Mixed' end

SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databaseswhere database_id=2

IF (serverproperty('InstanceName')) IS NOT NULL

BEGIN

SET@insname='mssql$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))

SET@agentname='sqlagent$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))

END

ELSE

BEGIN

SET @insname='mssqlserver'

SET @agentname='sqlserveragent'

END

SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@insname+' | FIND "STATE"'''

INSERT ##sqlservice

EXEC (@sctsql)

SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@agentname+' | FIND "STATE"'''

INSERT ##agentservice

EXEC (@sctsql)

IF EXISTS(SELECT 1 FROM ##sqlservice WHERE details LIKE'%RUNNING%')

SET @sqlstatus = 'Running'

ELSE

SET @sqlstatus = '<font color="red">Not Running</font>'

IF EXISTS(SELECT 1 FROM ##agentservice WHERE details LIKE'%RUNNING%')

SET @agentstatus = 'Running'

ELSE

SET @agentstatus = '<font color="red">Not Running</font>'

IF EXISTS (SELECT 1 FROM sys.databases WHEREstate_desc<>'ONLINE')

BEGIN

SET @dbstatus= '<font color="red">Some of the database(s) are offline</font>'

SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>'

SELECT @dbdetail = @dbdetail + '<td align="Center">' + name + '</td><td align="Center">'+state_desc+'</td></tr>

</table>' FROM sys.databases WHERE state_desc<>'ONLINE'

END

ELSE

BEGIN

SET @dbdetail = ''

SET @dbstatus='All databases are online'

END

SET @subject=@sname+' : DB Monitor Alert'

SET @HTML=

N'<style type="text/css">

#box-table

{

font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;

font-size: 12px;

text-align: center;

border-collapse: collapse;

border-top: 7px solid #9baff1;

border-bottom: 7px solid #9baff1;

}

#box-table th

{

font-size: 13px;

font-weight: normal;

background: #b9c9fe;

border-right: 2px solid #9baff1;

border-left: 2px solid #9baff1;

border-bottom: 2px solid #9baff1;

color: #039;

}

#box-table td

{ border-right: 1px solid #aabcfe;

border-left: 1px solid #aabcfe;

border-bottom: 1px solid #aabcfe;

color: #669;

}

tr:nth-child(odd) { background-color:#eee; }

tr:nth-child(even) { background-color:#fff; }

</style>'+

'<h3><font color="Green"> -:: '+@sname+' ::- </h3><br>'+'

<tr><font color="Green"

<th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th>

</tr>

<tr><td align="Center">'+@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td>

</tr>

</table>

<br>'

if (@dbcount >0 )

begin

set @HTML=@HTML + N'<H3><font color="Red"> -:: Off-Line Database Details ::- </H3>' +

N'<table id="box-table" >' +

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>

</tr>' +

CAST ( (

SELECT td = CAST([name] AS VARCHAR(200)),'',

td = [state_desc],'',

td = CONVERT(VARCHAR(30),[create_date],120)

FROM sys.databases WHERE state_desc <> 'ONLINE'

ORDER BY [name]

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' +

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'

<tr><font color="Green"

<th>Server Name</th>

<th>Job Status</th>

<th>Last Run Status</th>

<th>Run Duration</th>

</tr>' +

CAST ( (

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) ASVARCHAR(200)),'',

td = j.name,'',

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

WHEN 4 THEN 'In Progress' ELSE

'Status Unknown' END ,'',

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration %100 * 10) / 216e4,108),'-')

FROM(msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id =jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id) frommsdb.dbo.sysjobactivity) and j.job_id in ( select job_id frommsdb.dbo.sysjobs where enabled=1)

ORDER BY j.name

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +

N'</table>'

end

else

set @HTML=@HTML + N'<H3><font color="Green"> -:: Off-Line Database Details ::- </H3>' +

N'<table id="box-table" >' +

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>

</tr>'+

CAST ( (

SELECT td = 'No Database Available','',

td = '-','',

td = CONVERT(VARCHAR(30),getdate(),120)

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' +

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'

<tr><font color="Green"

<th>Server Name</th>

<th>Job Status</th>

<th>Last Run Status</th>

<th>Run Duration</th>

</tr>' +

CAST ( (

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) ASVARCHAR(200)),'',

td = j.name,'',

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

WHEN 4 THEN 'In Progress' ELSE

'Status Unknown' END ,'',

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration %100 * 10) / 216e4,108),'-')

FROM(msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id =jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id) frommsdb.dbo.sysjobactivity) and j.job_id in ( select job_id frommsdb.dbo.sysjobs where enabled=1)

ORDER BY j.name

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +

N'</table>'

print @HTML

--print @tableHTML

EXEC msdb.dbo.sp_send_dbmail

@recipients ='dbaadmin@abc.com', --replace with your email id

@subject=@subject,

@body =@HTML,

@body_format ='HTML'

About Author:

Raghwendra Mishra is DBA who works as associate consultant with Systems Plus Pvt. Ltd. He in free time reads and write on various web technologies. He can be contacted at: raghwendra.mishra@spluspl.com

SYSTEMS +

Thursday 16 October 2014

SQL SERVER Monitoring Script

1 comment: