Automating SQL Server Health Check (SQL Server 2005)

Question

Automating SQL Server Health Check (SQL Server 2005)

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

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

david.beechum SSC Enthusiast Points: 169 More actions · Answer 1

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?

cm62597 SSC Eights! Points: 976 More actions · Answer 2

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.

Chad

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 3

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

nplace6530 Ten Centuries Points: 1050 More actions · Answer 4

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

With:

select sj.job_id, sj.name, sc.name 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!!!

Nathan Vorhies Grasshopper Points: 11 More actions · Answer 5

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.

hugo sesoldi SSC Journeyman Points: 92 More actions · Answer 6

Hi all.

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

Everything else works ok.

Any solutions?

bookend2 Grasshopper Points: 14 More actions · Answer 7

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.

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 8

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.

bibbyp Newbie Points: 9 More actions · Answer 9

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?

Lindsay Stuckey SSC Enthusiast Points: 114 More actions · Answer 10

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.

Mike D Say Hey Kid Points: 701 More actions · Answer 11

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?

Thanks

nplace6530 Ten Centuries Points: 1050 More actions · Answer 12

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

M&M SSC-Insane Points: 21700 More actions · Answer 13

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

M&M

Mike D Say Hey Kid Points: 701 More actions · Answer 14

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.

Lindsay Stuckey SSC Enthusiast Points: 114 More actions · Answer 15

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.