Automating SQL Server Health Check (SQL Server 2005)

  • How long should this script take to run? I gave up after 45 minutes and stopped the query execution and got the same error as everyone else: "An INSERT EXEC statement cannot be nested". This error is thrown by the statement on line 109. "INSERT #jobs_status EXEC msdb.dbo.sp_help_job"

    I ran the query again and inserted some PRINT statements to help figure out where it is hanging up, the statement beginning on line 352 doesn't seem to ever complete. The next print statement is line 378 which never happens.

    Any ideas?

  • I got the following error.

    An INSERT EXEC statement cannot be nested. [SQLSTATE 42000] (Error 8164). The step failed.

    Do know what I am doing wrong. This could be a huge help to me. Thanks for posting this.


  • Nice article. I've done the opposite approach instead of having the servers all email me I poll each server and keep a central database repository of this type of information. On the central database I have ssrs reports that get me a quick glance at my 100+ servers so I know which children are behaving badly. I also have a job that scans through the data and alerts me for conditions that need quicker attention incase I get distracted by other issues.

    The thing I like about my approach is I get less email and only the email that really needs my attention.

    I've learned over the years that it is definately much better to be proactive than reactive. Taking care of your children (databases) before the become problem children is a must as a DBA.

    Either approach you take will help you to be less of a fire fighter and more of a fire prevention manager.

    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Hi All

    I had a quick look into the "An INSERT EXEC statement cannot be nested" error.

    Yes this error does not stop the report from running but if you schedule a job that runs this procedure the scheduled job will report a failure. So the error needs to be removed.

    I replaced this line in the original code:

    INSERT #jobs_status EXEC msdb.dbo.sp_help_job


    select sj.job_id,, as Category, sj.Enabled, sjs.last_run_outcome,

    (select max(run_date) from sysjobhistory sjh where sjh.job_id = sj.job_id) as last_run_date

    into #jobs_status

    from sysjobs sj

    join syscategories sc

    on sj.category_id = sc.category_id

    join msdb.dbo.sysjobservers sjs

    on sjs.job_id = sj.job_id

    This gets rid of the "An INSERT EXEC statement cannot be nested" error.

    If there are any issues with the above code please let me know.

    This is my first post so be kind:-D!!!

  • Is there a simple way to export the final HTML table to an HTML file? Because of business restrictions, we can't use DB Mail or send email that includes html, BUT, if I could export to an HTML file, I could then attach the html file and email it.

  • Hi all.

    Can`t get the mail to send the body of the msg.

    Everything else works ok.

    Any solutions?

  • I have the same issue as Grasshopper; the job will run successfully, but it won't send the mail.

    I did change the mail profile name and recipient to what's configured on the SQL Server, but this script will run everything successfully but except for sending teh email.

    As FYI, I did do a test-email send, and that was successful.

  • The most important concept here is that a responsible DBA should be monitoring the health and activity of the data servers proactively. Over the past 10 years I have seen many solution from expensive 3rd party applications to home grown solutions such as this. The past the positions I have had involved monitoring from a few to over 30 data servers each with multiple databases. The first solution I employed was an ASP interface. The last two I used SSRS (Reporting Services) to provide a "live" dashboard which monitored SQL 2000 to SQL 2008 databases. All of the servers send notifications via e-mail to the IT department if a job fails. The dashboards all had links to "Run Sheets" for the jobs which included recovery and notification steps.

    The dashboard gives an overall status and health for all of the production databases. Each data server name is color coded from green to red with a status number from 0 to 7. When you click on the server name a new report showing all of the details is rendered. The report includes a list of failed jobs with links to run sheets; drive letter with total space used, free space and % free; a Defrag and Check Disk analysis (runs once a month); uptime; SQL version. Each database name can be expanded within the report to display logical name, file name, segment, space used, size, max size, next allocation, growth type and total size.

    All of what is displayed in these types of reports depends on the organizations and departments needs. I donโ€™t think there is one perfect solution for everyone. Like any application development lifecycle you really need to sit down with key stake holders and go through the requirements gathering process.

  • this sends me an email, but get the following when running exec uspEmailSQLSErverHealth:

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

    An INSERT EXEC statement cannot be nested.

    Mail queued.

    Any ideas?

  • Hi, Thanks for the great article. I have been playing with the script tweaking it for what I would like. I do have 1 issue. I keep getting the following error which is preventing me from setting this up as a job. Hopefully someone will have an idea for me to try.

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

    An INSERT EXEC statement cannot be nested.

    Mail queued.

  • Pasted SP and get the following when running it...

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

    An INSERT EXEC statement cannot be nested.

    Mail queued.

    Yet is seems to work. Any ideas?


  • There is a solution to the "An INSERT EXEC statement cannot be nested" problem in a previous post. Just read up a few posts.

  • Please let me know how much time this procedure takes to run.


  • I tried the fix, now I get the following when I try to create the SP:

    Msg 2714, Level 16, State 1, Procedure uspEmailSQLServerHealth, Line 112

    There is already an object named '#jobs_status' in the database.

  • Thanks, After I posted I found the fix to the Exec statement error. ๐Ÿ˜€ I also made 1 more change to fix the issue. I commented out the CREATE TABLE #jobs_status statement so that the table would not be created twice. After that the procedure works great. Thanks again to everyone for the great ideas.

    Ritesh, Thanks to you for all your efforts to provide a great article.

Viewing 15 posts - 46 through 60 (of 140 total)

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