Automating SQL Server Health Check (SQL Server 2005)

  • For those that are interested in the length of time it takes to run. I shortened the length of time by changing the line that calculates the start and end dates. Also I modified the section to exclude logfile details and finally only display the individual lines for the backup locations not the individual files.


  • Hi Mike D

    run drop table #jobs_status

    The re run the procedure. That should sort out your issue.

  • If this procedure seems to run forever for you then I have a solution. The below section of the sp uses a date range that when left as the default date range gave me a one month period that returned just over 30,000 rows. I changed line 138 to the following: SELECT @StartDate = DATEADD("d",-1,GETDATE()) and the sp finished in about 15 seconds.


    @TableHTML = @TableHTML +


    <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),, '') +'</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>' +



    backupset MST


    MST.backup_start_date BETWEEN @StartDate AND @EndDate


    MST.backup_start_date DESC

  • Has anyone had problems with the email format in Outlook 2007? Mine si all jacked up, but looks OK when sent to my gmail account.

  • Yes the format is messed up for me too on Outlook 2007, I think there is a closing tag missing somewhere, maybe a </td>. I haven't had time to look through the html tags.

  • [font="Times New Roman"]Hi Ritesh,

    I implemented these automation on our servers and very happy with it.

    I got this message:

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

    An INSERT EXEC statement cannot be nested.

    Mail queued.

    Getting e-mail with no problem, even the job status failed.[/font]

  • Linda,

    Set the on fail to either proceed to next statement or to exit reporting success in the Job and it will work even with the annoying notice that is popping up.

  • I found that the initial tags for end table </table> after the first couple sections (Job Status, Databases, Disk Stats) was missing. Also, the header's for most of the sections where showing up in the same sell as the last column headings. I moved the headers to above the table definintions and that solved those issues for me.

  • Do you have the updated script to correct the headers within Outlook? Thanks

  • Here is the copy of the original script that I edited to fix the Section headers. (This does work in SS 2008)

    Due to forum restrictions I had to rename the SQL file as a .TXT file....

  • This is a nice concept. It could be useful to help save money or help a DBA get an idea to create a health check of his/her own.

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Getting this when i run this code:

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

    An INSERT EXEC statement cannot be nested.

    Mail queued.

    Any thoughts?

  • HI Ritesh,

    very nice over the couple of minor issues(email format issue and that nested thingy..)..

    well done and thank you.

  • @rithesh,

    Thanks. If i mananger to find smthng on CPU, i will post it. 🙂

    @Omprakash K Deshpande,

    SQL Alert helps monitoring fatal errors and SQL counters. CPU % falls under

    windows counter and hence cant be monitored using SQL Alert.

  • Hello,

    I customised the script for our company usage fiddling a bit with HTML.

    Also updated the script to exclude the nesting error.

    Seems to be working fine, thank you guys.

    It can definitely by extended and I will try.

    So far I managed to created two jobs on two different SQL instances and it works.

    You simply create the SP and the job on each instance and run the SP from the job with your SQL box IP address. It shows databases and the rest from the correct instance.

    There are third-party apps doing similar think but it's nice to be able to receive an email just to remind you about your tasks when busy with something else


Viewing 15 posts - 61 through 75 (of 140 total)

You must be logged in to reply to this topic. Login to reply