Automating SQL Server Health Check (SQL Server 2005)

Question

Post reply

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 16 through 30 (of 140 total)

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

Dale Turley Right there with Babe Points: 745 More actions · Answer 1

First off - great article and a really handy stored procedure..

When i executed the sp i got this error message

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

An INSERT EXEC statement cannot be nested.

Msg 242, Level 16, State 3, Procedure uspEmailSQLServerHealth, Line 126

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It did send me an email though so all's good.

Drazzing Grasshopper Points: 11 More actions · Answer 2

Hi

Just a suggestion why dont you use table variables instead of temp tables, might reduce the code and the initial checks.

mark_at_li SSC Enthusiast Points: 173 More actions · Answer 3

Dale Turley (1/28/2010)
First off - great article and a really handy stored procedure..
When i executed the sp i got this error message
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
Msg 242, Level 16, State 3, Procedure uspEmailSQLServerHealth, Line 126
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
It did send me an email though so all's good.

First off, great article. Thank you for sharing.

However I have encoutered the same issue as above and not yet resolved. (SQL 2005 standard edition)

Also quick correction on your example, just incase anyone doesnt click that

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

should be

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

Thanks again 😉

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 4

@ritesh-2,

That's not smthng i was looking at as it goes away from sql..and involves lot of manual configuring alert on each server. Also, when one monitors lot of servers, you would like to store these info at a consolidated single server along with other parameters.

Also, if we can have CPU monitoring we can have it as a part of a quick health check script as well

What I was looking was smthng like xp_fixeddrives. But anyways AFAIK, none of the extended sps do that.. Does anyone have a better solution?

Regards,
Raj

http://Strictlysql.blogspot.com

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 5

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 3:16 am

#1109831

thx for correction mark..

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 6

hey dale,

I am aware of, nothing to worry its just info msg. i havent tried finding the cause however its not a show stopper.

An INSERT EXEC statement cannot be nested.

regarding this one are you able to find the exact code whr it breaks?

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 7

@SRosewarne-795472 thx for the suggestion. I will change it on my servers....

Dale Turley Right there with Babe Points: 745 More actions · Answer 8

Thinking about it.. it may be to do with the language i use. It was on my test machine. I'll try it when i get back to the office.

Thanks

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 9

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 3:22 am

#1109838

@arr.nagaraj i will work on CPU thing

Omprakash K Deshpande Say Hey Kid Points: 681 More actions · Answer 10

for CPU utilization use sql alert. In SQL alert we can set whenever cpu utilization increases beyond the specified range. In that alert message try to execute a job to gather full information.