SQL Server Monitoring – Automating SQL Server Health Checks (SQL Server 2005 & Above)

It’s a known fact that an ideal Production DBA is like a parent to 100’s of children. By children I mean database servers, and yes, they are like babies to DBAs. No matter how unpleasant your child is you have to take care of each of them. As a DBA, it’s your responsibility to keep an eye on each and every activity that is taking place on your database servers. It could be the creation of new tables, databases, logins, jobs, maintenance plans and so on.

In this article I will be focusing on database some of the SQL Server monitoring activities (listed below), which I am supposed to carry out on day to day basis in my organization and I believe many other DBAs in their organizations.

  • Ensuring jobs have run successfully; also check if any job is running in endless loop, check if any new job has been created.
  • Check whether the backups have happened.
  • Ensure that the backups are deleted as per the retention policy from the disk and there are no unnecessary files lying on the disk which could cause backup failure.
  • Keep an eye on disk space.
  • Check if any new database has been created.
  • Check if there is drastic increase in size of any of the databases.

This article is targeted at production DBAs.

I used to carry out these activities in various manners, which also involved manual interventions. I somehow wanted to automate this activity and get all the required information in well formatted (HTML) e-mail.

This mail is divided into six sections as below,

Section 1 [Server Info]

This section features generic information like server name, SQL Server version, service pack, whether the server is in single mode, clustered, licensing information, etc.

How it works?

I have written appropriate comments at appropriate places inside the stored procedure; here I will focus only on the code that actually fetches the data from various system objects. I have declared a variable at the beginning of the stored procedure, @TableHTML, which gets built and then executed at the end before it sends an e-mail.

Here is the code sinppet for section 1, Server Info.

---------------------------- Section A ------------------------------
 SET @TableHTML =   
         '<font face="Verdana" size="4">Server Info</font>  
         <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">  
         <tr>  
         <td width="27%" height="22" bgcolor="#000080"><b>  
           <font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>  
         <td width="39%" height="22" bgcolor="#000080"><b>  
         <font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>  
         <td width="90%" height="22" bgcolor="#000080"><b>  
         <font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td> 
	         </tr>  
 ---------------------------- Section B ------------------------------
         <tr>  
         <td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>  
         <td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>  
         <td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>  
         </tr>  
         </table>

You will notice lots of HTML tags which have been used to make the e-mail look well formatted, however detailed discussion on these HTML tags is beyond the scope of this article. In a nutshell, these HTML tags are divided in to two sections, Section A, which builds the header information (Refer Fig1), and Section B that contains actual information fetched form various system objects.

If you are interested in learning HTML you can get help from any web designer or you can use any HTML editor like Microsoft FrontPage, which is the nice and easy to use application where you just have to drag and drop components, format, and align things. Then you can copy the HTML script generated by Frontpage and paste it in the stored procedure. I believe that you will not need to make any change to this code as it contains all the critical information an Ideal DBA desires.

The actual part to look over here is last couple of lines in Section B of the code where you will see variables concatenated with HTML script; this is the place where the actual data gets populated.

In case you need to remove any information you think is irrelevant to you, here is what you do. Let’s say you want to exclude the ServerIP from this section of the e-mail. All you have to do is to knock of the lines of code which I have highlighted in bold Section A and B. Similarly, if you want to add more information you can copy paste the highlighted lines in whichever sequence you want. For example, if you want to add service name in this section of e-mail, the code after change will look like this (refer the code highlighted in bold)

---------------------------- Section 1 ------------------------------
 SET @TableHTML =   
         '<font face="Verdana" size="4">Server Info</font>  
         <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">  
         <tr>  
         <td width="27%" height="22" bgcolor="#000080"><b>  
         <font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>  
 <font face="Verdana" size="2" color="#FFFFFF">ServiceName</font></b></td> 
         <td width="39%" height="22" bgcolor="#000080"><b>  
         <font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>  
         <td width="90%" height="22" bgcolor="#000080"><b>  
         <font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td> 
         </tr>  
 ---------------------------- Section 2 ------------------------------
f         <tr>  
         <td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>  
         <td width="27%" height="27"><font face="Verdana" size="2">'+@@ServiceName+'</font></td> 
         <td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>  
         <td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>  
         </tr>  
         </table>

You can refer to the stored procedure code at end of the post (EmailSQLServerHealth.sql) to see from where I got values for these variables, for example to get version information, I used system function @@version in this way.

SELECT @Version = @@version

For edition I used the system function serverproperty as:

SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))

How It Looks?

1_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Section 2 [Job Status]

This section features job status, whether they have executed successfully or have failed, and how long the execution took. Try to run this email on the server having jobs that fail, and also have disabled jobs. In such a scenario the disabled jobs are highlighted in a different color and failed jobs are highlighted in red with links (Refer Fig2). When you click the link on failed jobs, it will open your e-mail editor where you can send the failure information to the concerned person. You can put this as step in job itself, however consider the case of a database backup job. Whenever it fails it sends an email to group of people. Now you realize that the cause for failure is insufficient disk space and the only solution is to add more disk. You also know that disk procurement process may take some time depending upon the organization’s policy. If this takes a really long time then users will be irritated with the mails, and this is the reason why I have added this optional mailing feature.

How it works?

As many of you must be aware of the system procedure sp_help_job in the msdb database, which returns details pertaining to SQL Server jobs, I have made use of this SP to get the desired information. First I created a temp table (#jobs_status) with exact number of columns returned by this SP and then populated the temp table as below

INSERT #jobs_status EXEC msdb.dbo.sp_help_job

I then manipulate the information in the temp table along with joins to sysjobs and sysactivity system tables in the msdb database. Again the process remains the same; first build the @TableHTML variable with the header information and then the actual information from the temp table. Below is the code to fetch the job information from the temp table. The only difference here is that the result set will have single row for each job on the server and that’s the reason I have fethed the information from temp table (#jobs_status) instead of using variables. You will see case statements along with HTML script, which are used to highlight the rows depending upon the last execution status of the job.

SELECT
          @TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' +
                                     ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</font></td>' +   
         CASE enabled 
                  WHEN 0 THEN '<td bgcolor="#FFCC99"><b><font face="Verdana" size="1">False</font></b></td>'
                  WHEN 1 THEN '<td><font face="Verdana" size="1">True</font></td>' 
         ELSE '<td><font face="Verdana" size="1">Unknown</font></td>' 
         END  +  
         CASE last_run_outcome    
                  WHEN 0 THEN '<td bgColor="#ff0000"><b><blink><font face="Verdana" size="2">
                 <a href="mailto:servicedesk@mycompany.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=db.support@mycompany.com&body = SD please log this call to DB support,' + '%0A %0A' + '<<' + ISNULL(CONVERT(VARCHAR(100), name),'''') + '>> Job Failed on ' + @OriServer + '(' + @ServerIP + ')'+ '.' +'%0A%0A Regards,'+'">Failed</a></font></blink></b></td>'
                  WHEN 1 THEN '<td><font face="Verdana" size="1">Success</font></td>' 
                  WHEN 3 THEN '<td><font face="Verdana" size="1">Cancelled</font></td>' 
                  WHEN 5 THEN '<td><font face="Verdana" size="1">Unknown</font></td>' 
         ELSE '<td><font face="Verdana" size="1">Other</font></td>' 
         END  +  
        '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</font></td>' +  
         '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</font></td>' +
         '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</font></td> </tr>'  
 FROM
          #jobs_status A
         inner join (
                                     select
                                                A.job_id,
                                               datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes
                                     from
                                                msdb..sysjobactivity A
         inner join (
                                     select
                                               max(session_id) sessionid,
                                                job_id
                                     from
                                                msdb..sysjobactivity
                                     group by
                                                job_id
                                      ) B on a.job_id = B.job_id and a.session_id = b.sessionid
         inner join (
                                     select
                                               distinct name,
                                                job_id
                                     from
                                                msdb..sysjobs
                                      ) C on A.job_id = c.job_id
                                      ) X on A.job_id = X.job_id
 ORDER BY
          last_run_date DESC

As discussed above the failed job will have link that will take you to email editior with prefilled values. If you wish to change any of these values have a look at the code highlighted in bold.

How It Looks?

2_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Section 3 [Databases]

This section will contain a list of databases, with some useful attributes like creation date, database size, state (online, offline…) and recovery model. Simple yet powerful information, this will help a DBA to check if any new database has been created without his knowledge (the newly added database may require to be included in the backup strategy) or, if there is a drastic increase in database size.

How it works?

As far as process of building @TableHTML goes it remains same throughout. I have made use of catalog views sys.databases and sys.sysaltfiles (BOL is the best place to get in depth knowledge on these catalog views ).

select
 @TableHTML =  @TableHTML +  
         '<tr><td><font face="Verdana" size="1">' + ISNULL(name, '') +'</font></td>' +   
         '<td><font face="Verdana" size="1">' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</font></td>' +   
         '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</font></td>' +   
         '<td><font face="Verdana" size="1">' + ISNULL(state_desc, '') +'</font></td>' +   
         '<td><font face="Verdana" size="1">' + ISNULL(recovery_model_desc, '') +'</font></td></tr>'
 from
          sys.databases MST
         inner join (select b.name [LOG_DBNAME],
                                     CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)/1024) [Total Size GB]
                                     from sys.sysaltfiles A
                                     inner join sys.databases B on A.dbid = B.database_id
                                     group by b.name)AA on AA.[LOG_DBNAME] = MST.name
 order by
          MST.name

How It Looks?

3_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Section 4 [Disk Stats]

In this section you will get to know how much free space is left on individual disks.

How it works?

Over here I have made use of the undocumented extended stored procedure xp_fixeddrives, populated the temp table with the output, and manipulated in same way as discussed in the above sections.

INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives

How It Looks?

4_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Section 5 [SQL Server Database Backup Stats]

This is another essential section and a must for any DBA. This will help you know which databases were backed up, the backup filename, backup type (full, differential, log, etc.), start time, end time, and backup size. This information will be incremental and will contain a months worth of data.

How it works?

Another catalog view, backupsets, served the purpose out here.

How It Looks?

5_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Section 6 [Physical backup files]

This is yet another interesting aspect that is often overlooked. As far as standard backup process goes, backups are created on local or remote disks and subsequently copied to tape. If the backup size is in terabytes (which is the case with me), the tape backup will take a significant amount of time. During this period the backup file will be locked and your retention policy may not be able to delete the file from disk. If your retention policy says that there should be only 3 full backup files on disk at any given point in time, I need to ensure that the old files are deleted timely, otherwise, it could lead to backup failure, and this is the reason why I added this section. It shows me list of physical files residing on the server, and if there are more files than I expect, I will go and delete the files manually and find out the root cause and subsequently inform the concerned person.

How it works?

As many of us are aware that SQL server logs each and every detail that is exposed by catalog views. Even for backup, information like backup type, filename, backup start and end time, etc., are logged and can be fetched from backupset and backupmediafamily catalog views. For this section, I have fetched the physical file name and path of the backup from backupmediafamily catalog view and populated it into a temp table. As this catalog view returns the backp filename along with the path. With the use of CHARINDEX function I got rid of the filename inorder to get only the backup path.

For example, from the catalog view I got: D:\DBBAckups\FullDBBackups\TESTDB.BAK. By using CHARINDEX, I removed the file name to get the backup path: D:\DBBAckups\FullDBBackups\. I browsed thorugh this path with the use of DOS command DIR and the options /B/O:D, to get list of all the physical files existing under the given path. The code iterates to get the files names under each distinct path.

Here is the explanation of the options used with DIR command

  • /B – Uses bare format (no heading information or summary).
  • /O – List by files in sorted order.
  • /D – Same as wide but files are list sorted by column.

The code snippet is shown below

INSERT #url
SELECT DISTINCT
 SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('\', REVERSE(BMF.physical_device_name), 0))
from 
 backupset MST
 inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
where 
 MST.backup_start_date BETWEEN @startdate AND @enddate
select @Cnt = COUNT(*) FROM #url
WHILE @Cnt >0
BEGIN
SELECT @URL = url FROM #url WHERE idd = @Cnt
 SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D'''
INSERT #dirpaths SELECT 'PATH: ' + @URL
 INSERT #dirpaths
 EXEC (@Str)
 INSERT #dirpaths values('')
SET @Cnt = @Cnt - 1
end
DELETE FROM #dirpaths WHERE files IS NULL

How It Looks?

6_SQL_Server_Monitoring_Automating_SQL_Server_Health_Checks

Prerequisites

The following are required for my solution to work.

  • SQL Server 2005.
  • Valid Database Mail Profile.
  • Sysadmin privilege as this procedure refers to lots of system objects in msdb and master database.
  • xp_cmdshell should be enabled as the code executes DIR command.

What does this E- mail contain?

This mail gives me the bird’s eye view of database server on daily basis; you can schedule this mail multiple times as per your requirement.I have scheduled this mail early morning after all the backup tasks and other off-peak hour’s activities are done. Here is how to setup this automated health check e-mail.

Step 1: Run the EmailSQLServerHealth.sql (at the end of the post) inside the msdb database. This will create a procedure uspEmailSQLServerHealth. This procedure accepts 5 input parameters:

  • @ServerIP – SQL Server 2005 Database Server IP Address
  • @Project – Name of project or client
  • @Recipients – Recipient(s) of this email (; separated in case of multiple recipients).
  • @MailProfile – Mail profile name which exists on the target database server
  • @Owner – Owner, basically name/email of the DBA responsible for the server

Step 2: Schedule a job as per your requirement with a call to this stored procedure. For example:

exec EmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile', 'My Self'

Why should I use it?

As I mentioned, an ideal production DBA is responsible for looking after a considerable amount of database servers. It is very handy to have such an e-mail scheduled on each server, given the benefit and the time taken to set this up instead of managing the admin tasks in a different manner on each server. It also saves great amount of time on monitoring activity, I have set up this e-mail on 20+ servers. Earlier I use to spend about 45 minutes on average looking at each server. All you need to do is create the stored procedure inside msdb database and schedule a job.

In the absence of serious diseases of the cardiovascular, nervous or circulatory systems, as well as the kidneys and liver, https://cialico.com does not cause side effects.

Resource (EmailSQLServerHealth.SQL)

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].uspEmailSQLServerHealth
(
@ServerIP VARCHAR(100), -- SQL Server 2005 Database Server IP Address
@Project VARCHAR(100), -- Name of project or cleint 
@Recepients VARCHAR(2000), -- Recepient(s) of this email (; separated in case of multiple recepients).
@MailProfile VARCHAR(100), -- Mail profile name which exists on the target database server
@Owner VARCHAR(200) -- Owner, basically name/email of the DBA responsible for the server
)  

/*
 
exec EmailSQLServerHealth '10.10.10.10',  'MYProject', 'myself@mycompany.com', 'TestMailProfile', 'My Self'
 
 
*/
 
AS   
BEGIN
	 
SET NOCOUNT ON
	 
/* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released, 
however, good to follow this practice). */
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#jobs_status')    
BEGIN   
    DROP TABLE #jobs_status    
	END   
 
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#diskspace')    
BEGIN   
    DROP TABLE #diskspace
	END   
	   
	IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#url')    
	BEGIN   
	    DROP TABLE #url
	END   
	 
IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#dirpaths')    
BEGIN   
    DROP TABLE #dirpaths
	END   
 
-- Create the temp tables which will be used to hold the data. 
CREATE TABLE #url
(
    idd INT IDENTITY (1,1), 
    url VARCHAR(1000)
)
	 
CREATE TABLE #dirpaths 
(
    files VARCHAR(2000)
)
 
CREATE TABLE #diskspace
(
    drive VARCHAR(200), 
    diskspace INT
)
 
-- This table will hold data from sp_help_job (System sp in MSDB database)
CREATE TABLE #jobs_status    
(    
    job_id UNIQUEIDENTIFIER,    
    originating_server NVARCHAR(30),    
    name SYSNAME,    
    enabled TINYINT,    
    description NVARCHAR(512),    
    start_step_id INT,    
    category SYSNAME,    
    owner SYSNAME,    
    notify_level_eventlog INT,    
    notify_level_email INT,    
    notify_level_netsend INT,    
    notify_level_page INT,    
    notify_email_operator SYSNAME,    
    notify_netsend_operator SYSNAME,    
    notify_page_operator SYSNAME,    
    delete_level INT,    
    date_created DATETIME,    
    date_modified DATETIME,    
    version_number INT,    
    last_run_date INT,    
    last_run_time INT,    
    last_run_outcome INT,    
    next_run_date INT,    
    next_run_time INT,    
    next_run_schedule_id INT,    
    current_execution_status INT,    
    current_execution_step SYSNAME,    
    current_retry_attempt INT,    
    has_step INT,    
    has_schedule INT,    
    has_target INT,    
    type INT   
)    
 
-- To insert data in couple of temp tables created above.
INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives     
INSERT #jobs_status EXEC msdb.dbo.sp_help_job  
 
-- Variable declaration   
DECLARE @TableHTML  VARCHAR(MAX),    
        @StrSubject VARCHAR(100),    
        @Oriserver VARCHAR(100),
        @Version VARCHAR(250),
        @Edition VARCHAR(100),
        @ISClustered VARCHAR(100),
        @SP VARCHAR(100),
        @ServerCollation VARCHAR(100),
        @SingleUser VARCHAR(5),
        @LicenseType VARCHAR(100),
        @StartDate DATETIME,
        @EndDate DATETIME,
        @Cnt int,
        @URL varchar(1000),
        @Str varchar(1000)
	         
-- Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)
SELECT @StartDate = @StartDate - 1
SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)  
SET @Cnt = 0
	 
IF serverproperty('IsClustered') = 0 
BEGIN
    SELECT @ISClustered = 'No'
	END
ELSE
BEGIN
	    SELECT @ISClustered = 'YES'
END
	 
SELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))
SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation')) 
SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) 
SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser')
                        WHEN 1 THEN 'Yes'
                        WHEN 0 THEN 'No'
                        ELSE
                        'null' END
SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))  
SELECT @strSubject = 'DB Server Daily Health Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'   
   
/*
Along with refrences to SQL Server System objects, You will also see lots of HTML code however do not worry, 
Even though I am a primarily a SQL Server DBA, I am little fond of HTML, 
so thought to show some of my HTML skills here :), trust me you would love to see the end product....
*/
SET @TableHTML =    
   '<font face="Verdana" size="4">Server Info</font>  
   <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">  
    <tr>  
    <td width="27%" height="22" bgcolor="#000080"><b>  
    <font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>  
    <td width="39%" height="22" bgcolor="#000080"><b>  
    <font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>  
    <td width="90%" height="22" bgcolor="#000080"><b>  
    <font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td>  
    </tr>  
    <tr>  
    <td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>  
    <td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>  
    <td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>  
    </tr>  
    </table> 
 
    <table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
    <tr>
    <td width="50%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">Version</font></b></td>
    <td width="17%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">Edition</font></b></td>
    <td width="18%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">Service Pack</font></b></td>
    <td width="93%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">Collation</font></b></td>
    <td width="93%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">LicenseType</font></b></td>
    <td width="30%" bgColor="#000080" height="15"><b>
    <font face="Verdana" color="#ffffff" size="1">SingleUser</font></b></td>
    <td width="93%" bgColor="#000080" height="15"><b>
<font face="Verdana" color="#ffffff" size="1">Clustered</font></b></td>
    </tr>
    <tr>
    <td width="50%" height="27"><font face="Verdana" size="1">'+@version +'</font></td>
    <td width="17%" height="27"><font face="Verdana" size="1">'+@edition+'</font></td>
    <td width="18%" height="27"><font face="Verdana" size="1">'+@SP+'</font></td>
    <td width="17%" height="27"><font face="Verdana" size="1">'+@ServerCollation+'</font></td>
    <td width="25%" height="27"><font face="Verdana" size="1">'+@LicenseType+'</font></td>
    <td width="25%" height="27"><font face="Verdana" size="1">'+@SingleUser+'</font></td>
    <td width="93%" height="27"><font face="Verdana" size="1">'+@isclustered+'</font></td>
    </tr>
    </table>

    <p style="margin-top: 0; margin-bottom: 0"> </p>' +    
    '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">  
    <tr>  
    <th align="left" width="432" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Job Name</font></th>  
    <th align="left" width="91" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Enabled</font></th>  
    <th align="left" width="85" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Last Run</font></th>  
    <th align="left" width="183" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Category</font></th>  
    <th align="left" width="136" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Last Run Date</font></th>  
    <th align="left" width="136" bgColor="#000080">  
    <font face="Verdana" size="1" color="#FFFFFF">Execution Time (Mi)</font></th>  
    </tr>
    <font face="Verdana" size="4">Job Status</font>' 
   
SELECT
    @TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' + 
                ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</font></td>' +    
    CASE enabled  
        WHEN 0 THEN '<td bgcolor="#FFCC99"><b><font face="Verdana" size="1">False</font></b></td>' 
        WHEN 1 THEN '<td><font face="Verdana" size="1">True</font></td>' 
	    ELSE '<td><font face="Verdana" size="1">Unknown</font></td>' 
    END  +   
    CASE last_run_outcome     
        WHEN 0 THEN '<td bgColor="#ff0000"><b><blink><font face="Verdana" size="2">
	        <a href="mailto:servicedesk@mycompany.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=db.support@mycompany.com&body = SD please log this call to DB support,' + '%0A %0A' + '<<' + ISNULL(CONVERT(VARCHAR(100), name),'''') + '>> Job Failed on ' + @OriServer + '(' + @ServerIP + ')'+ '.' +'%0A%0A Regards,'+'">Failed</a></font></blink></b></td>'
	        WHEN 1 THEN '<td><font face="Verdana" size="1">Success</font></td>' 
        WHEN 3 THEN '<td><font face="Verdana" size="1">Cancelled</font></td>' 
        WHEN 5 THEN '<td><font face="Verdana" size="1">Unknown</font></td>' 
    ELSE '<td><font face="Verdana" size="1">Other</font></td>' 
    END  +   
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</font></td>' +   
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</font></td>' +
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</font></td> </tr>'  
FROM
   #jobs_status A
    inner join (
                select
                    A.job_id,
                    datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes 
                from
                    msdb..sysjobactivity A
    inner join (
                select
                    max(session_id) sessionid,
                    job_id 
               from
                    msdb..sysjobactivity 
                group by
                    job_id
                ) B on a.job_id = B.job_id and a.session_id = b.sessionid
    inner join (
                select
                    distinct name, 
                    job_id 
                from
                    msdb..sysjobs
                ) C on A.job_id = c.job_id
                ) X on A.job_id = X.job_id
ORDER BY
   last_run_date DESC 
 
SELECT
    @TableHTML =  @TableHTML + 
    '<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
      <tr>
        <td width="35%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">Name</font></b></td>
        <td width="23%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">CreatedDate</font></b></td>
        <td width="23%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">DB Size(GB)</font></b></td>
        <td width="30%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">State</font></b></td>
        <td width="50%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">RecoveryModel</font></b></td>
      </tr>
    <p style="margin-top: 1; margin-bottom: 0"> </p>
    <font face="Verdana" size="4">Databases</font>'
 
select
@TableHTML =  @TableHTML +   
    '<tr><td><font face="Verdana" size="1">' + ISNULL(name, '') +'</font></td>' +    
    '<td><font face="Verdana" size="1">' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</font></td>' +    
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</font></td>' +    
    '<td><font face="Verdana" size="1">' + ISNULL(state_desc, '') +'</font></td>' +    
    '<td><font face="Verdana" size="1">' + ISNULL(recovery_model_desc, '') +'</font></td></tr>'
from
    sys.databases MST
    inner join (select b.name [LOG_DBNAME], 
                CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)/1024) [Total Size GB]
                from sys.sysaltfiles A
                inner join sys.databases B on A.dbid = B.database_id
                group by b.name)AA on AA.[LOG_DBNAME] = MST.name
order by
    MST.name

SELECT
    @TableHTML =  @TableHTML + 
    '<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="24%" border="1">
      <tr>
        <td width="27%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">Disk</font></b></td>
        <td width="59%" bgColor="#000080" height="15"><b>
        <font face="Verdana" size="1" color="#FFFFFF">Free Space (GB)</font></b></td>
      </tr>
    <p style="margin-top: 1; margin-bottom: 0"> </p>
    <p><font face="Verdana" size="4">Disk Stats</font></p>'

SELECT
    @TableHTML =  @TableHTML +   
    '<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</font></td>' +    
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ISNULL(CAST(CAST(diskspace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),'') +'</font></td></tr>'
FROM
    #diskspace
 
SELECT @TableHTML =  @TableHTML + '</table>'
 
-- Code for SQL Server Database Backup Stats
SELECT
    @TableHTML = @TableHTML +   
    '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">    
    <tr>    
    <th align="left" width="91" bgColor="#000080">    
    <font face="Verdana" size="1" color="#FFFFFF">Date</font></th>    
    <th align="left" width="105" bgColor="#000080">    
    <font face="Verdana" size="1" color="#FFFFFF">Database</font></th>    
    <th align="left" width="165" bgColor="#000080">    
     <font face="Verdana" size="1" color="#FFFFFF">File Name</font></th>    
    <th align="left" width="75" bgColor="#000080">    
     <font face="Verdana" size="1" color="#FFFFFF">Type</font></th>    
    <th align="left" width="165" bgColor="#000080"> 
    <font face="Verdana" size="1" color="#FFFFFF">Start Time</font></th>    
    <th align="left" width="165" bgColor="#000080">    
    <font face="Verdana" size="1" color="#FFFFFF">End Time</font></th>    
    <th align="left" width="136" bgColor="#000080">    
   <font face="Verdana" size="1" color="#FFFFFF">Size(GB)</font></th>    
    </tr> 
<p style="margin-top: 1; margin-bottom: 0"> </p>
    <p><font face="Verdana" size="4">SQL SERVER Database Backup Stats</font></p>'
 
 
SELECT
   @TableHTML =  @TableHTML +     
   '<tr>  
    <td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + '-' + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),'') +'</font></td>' +      
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</font></td>' +      
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</font></td>' +   
    CASE Type 
    WHEN 'D' THEN '<td><font face="Verdana" size="1">' + 'Full' +'</font></td>'   
    WHEN 'I' THEN '<td><font face="Verdana" size="1">' + 'Differential' +'</font></td>'
    WHEN 'L' THEN '<td><font face="Verdana" size="1">' + 'Log' +'</font></td>'
    WHEN 'F' THEN '<td><font face="Verdana" size="1">' + 'File or Filegroup' +'</font></td>'
    WHEN 'G' THEN '<td><font face="Verdana" size="1">' + 'File Differential' +'</font></td>'
    WHEN 'P' THEN '<td><font face="Verdana" size="1">' + 'Partial' +'</font></td>'
    WHEN 'Q' THEN '<td><font face="Verdana" size="1">' + 'Partial Differential' +'</font></td>'
    ELSE '<td><font face="Verdana" size="1">' + 'Unknown' +'</font></td>'
    END + 
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</font></td>' +  
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</font></td>' +  
    '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</font></td>' +  
     '</tr>'    
FROM
    backupset MST
WHERE
    MST.backup_start_date BETWEEN @StartDate AND @EndDate
ORDER BY
    MST.backup_start_date DESC
 
SELECT @TableHTML =  @TableHTML + '</table>'
 
-- Code for physical database backup file present on disk
INSERT #url
SELECT DISTINCT
    SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('\', REVERSE(BMF.physical_device_name), 0))
from 
    backupset MST
    inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
where 
    MST.backup_start_date BETWEEN @startdate AND @enddate
 
select @Cnt = COUNT(*) FROM #url
 
WHILE @Cnt >0
BEGIN
 
   SELECT @URL = url FROM #url WHERE idd = @Cnt
    SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D'''
	 
    INSERT #dirpaths SELECT 'PATH: ' + @URL
    INSERT #dirpaths
     
    EXEC (@Str)
	     
   INSERT #dirpaths values('')
 
    SET @Cnt = @Cnt - 1
     
end
 
DELETE FROM #dirpaths WHERE files IS NULL

select 
    @TableHTML = @TableHTML +   
    '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">    
    <tr>    
    <th align="left" width="91" bgColor="#000080">    
    <font face="Verdana" size="1" color="#FFFFFF">Physical Files</font></th>
    </tr>
<p style="margin-top: 1; margin-bottom: 0"> </p>
    <p><font face="Verdana" size="4">Physical Backup Files</font></p>'    
 
SELECT 
    @TableHTML =  @TableHTML + '<tr>'  + 
    CASE SUBSTRING(files, 1, 5) 
        WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7"><b><font face="Verdana" size="1">' + files  + '</font><b></td>' 
    ELSE 
        '<td><font face="Verdana" size="1">' + files  + '</font></td>' 
    END + 
    '</tr>'  
FROM 
    #dirpaths  
 
SELECT 
    @TableHTML =  @TableHTML + '</table>' +   
    '<p style="margin-top: 0; margin-bottom: 0"> </p>
    <hr color="#000000" size="1">
    <p><font face="Verdana" size="2"><b>Server Owner:</b> '+@owner+'</font></p>  
    <p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks   
    and Regards,</font></p>  
    <p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">DB   
    Support Team</font></p>  
    <p> </p>'  
 
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = @MailProfile,    
    @recipients=@Recepients,    
    @subject = @strSubject,    
    @body = @TableHTML,    
    @body_format = 'HTML' ;    
	 
SET NOCOUNT OFF
END

 

Regards

Ritesh Medhe

Like us on FaceBook | Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

8 Comments on “SQL Server Monitoring – Automating SQL Server Health Checks (SQL Server 2005 & Above)”

  1. When i try to execute the procedure , it gives me an error saying

    Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72

    An INSERT EXEC statement cannot be nested.

    can u plz help me with this

  2. Ritesh,

    Thanks very much. This is very handy script to monitor the servers.

    Hi Mujahid,

    Even i got the same error when i tried to execute the stored procedure. In order to fix that i made a change to the code while inserting the stored prodeure execution results data into a temp table and the error didnt occur again.Please see below what i have modified.

    I modified this “INSERT #jobs_status EXEC msdb.dbo.sp_help_job” code with the code below and it worked.

    INSERT

    #jobs_status

    SELECT

    * FROM OPENROWSET

    (‘SQLOLEDB’,’Server=(local);TRUSTED_CONNECTION=YES;’,’set fmtonly off EXEC msdb.dbo.sp_help_job’)

    Ritesh,

    Please correct me if i am wrong here.

    Thanks,

    Abdul

  3. I am new to SQL Server – coming from DB2 Mainframe background and this was a life saver

    Thank you so much, works perfectly for 2008 R 2

  4. I’ve had this running for about 2 years now and its worked briliantly. I’ve even modified it to show jobs running and the error log amongst other things. However we just migrated to Exchange 2010 and it wraps it all the html tables around. Must be some tags it doesn’t understand. I would attach a screen shot if I could. Any ideas?

  5. Hi …. This is really great …. please advice how can we populate the result into excel sheet rather than Email. Thanks in advance !

  6. INSERT #jobs_status
    SELECT * FROM OPENROWSET (‘SQLOLEDB’,’Server=(local);TRUSTED_CONNECTION=YES;’,’set fmtonly off EXEC msdb.dbo.sp_help_job’)
    Having error:

    Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
    The metadata could not be determined because statement ‘EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT’ in procedure ‘sp_is_sqlagent_starting’ invokes an extended stored procedure.

  7. I configured in one of server and job runs perfectly, but when I receive email the design is not properly arranged tried doing some fixes but still same issue I am getting, could you please help?

Leave a Reply to Ramdas Cancel reply

Your email address will not be published. Required fields are marked *