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.
CREATE PROC ServerInfo
AS
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) ,@tableHTML VARCHAR(8000)
DECLARE @dbcount int
select @dbcount=count (*) FROM sys.databases WHERE state_desc <>
'ONLINE'
SELECT @sname = @@SERVERNAME
SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly')
when 1 then 'Windows' else 'Mixed' end
SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databases where 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 WHERE state_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>'+'
<table
id="box-table">
<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>'+'
<table
id="box-table">
<tr><font
color="Green"
<th>Server
Name</th>
<th>Job
Name</th>
<th>Job
Status</th>
<th>Last Run
Status</th>
<th>Last
Date Run</th>
<th>Run
Duration</th>
</tr>' +
CAST
( (
SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(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) from msdb.dbo.sysjobactivity) and j.job_id in ( select job_id from msdb.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>'+'
<table
id="box-table">
<tr><font
color="Green"
<th>Server
Name</th>
<th>Job
Name</th>
<th>Job
Status</th>
<th>Last Run
Status</th>
<th>Last
Date Run</th>
<th>Run
Duration</th>
</tr>' +
CAST
( (
SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(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) from msdb.dbo.sysjobactivity) and j.job_id in ( select job_id from msdb.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'
No comments:
Post a Comment