Comments posted to this topic are about the item <A HREF="/articles/Automating+SQL+Server+Health+Checks/68910/">Automating SQL Server Health Check (SQL Server 2005) </A>

!!!!!!!!!!!!!!!! Please try this modified script. This version contains fix to all the issues reported by people.

Vote Of Thanks!!!!!!!

Thanks for all the posts. I really appretiate and I hope that this article will be helpful for DBAs and make their life little easy.

Special thanks (For making my solution perfect and fixing the bugs )
@Mark-1022992:
For pointing out

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

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

@SRosewarne-795472: For recommending table vairable.

@nplace6530: For fixing
An INSERT EXEC statement cannot be nested

@david.beechum: reducing the backup stats range from 1 month to one day this has certainly improved the performance.

@timothy.shawley: For fixing the HTML tags issue.

Thanks a lot guys.......

Hi Ritesh,


I also implemented these automation on our servers. I think you missed few more things like when new db created, dropped and altered on the server, reading sql server error log, login creation and dropped. Depending on our requirement we can automate few more tasks. Its a good example for other DBA to take a look and make there routine work easy. In this method a single DBA can maintain multiple servers and database simultaneously and also increases DBA productivity.

Thanks
Omprakash

Hi
I am a novice DBA but I was thrilled to read your article and found it very helpful. This gives me an opportunity to automate my SQL health checklist, I wonder if it is possible to create a realtime dashboard to monitor database and server performance related issues?

I dislike that the article contains this.

"use any HTML editor like Microsoft FrontPage, which is the nice and easy to use application"

That looses a lot of credibility, especially since creating html is not the focal point of the task.

The article itself provides good information on a subject that's very important for the (often unintentional) DBA; that is keeping an eye on things. It could do with an update to ss2008.

thanks OM. I totally agree with you the items you mentioned are core DBA activities. This article just features basic items which need to be monitored on any DB server. This is good to start with for people who have no monitoring in place.

there are lots of third party tools...

Hello,

thanks for the promising looking script! But at least for me this script is not working and fails with error..even I have just copy&paste the the script ..Did I something wrong?

Errors I've got:
Msg 137, Level 15, State 1, Procedure uspEmailSQLServerHealth, Line 146
Must declare the scalar variable "@OriServer".
Msg 137, Level 15, State 1, Procedure uspEmailSQLServerHealth, Line 147
Must declare the scalar variable "@strSubject".
Msg 137, Level 15, State 2, Procedure uspEmailSQLServerHealth, Line 167
Must declare the scalar variable "@OriServer".
Msg 137, Level 15, State 2, Procedure uspEmailSQLServerHealth, Line 228
Must declare the scalar variable "@OriServer".
Msg 102, Level 15, State 1, Procedure uspEmailSQLServerHealth, Line 261
Incorrect syntax near 'X'.
Msg 137, Level 15, State 2, Procedure uspEmailSQLServerHealth, Line 382
Must declare the scalar variable "@startdate".
Msg 137, Level 15, State 2, Procedure uspEmailSQLServerHealth, Line 430
Must declare the scalar variable "@owner".
Msg 137, Level 15, State 2, Procedure uspEmailSQLServerHealth, Line 440
Must declare the scalar variable "@strSubject".

Thanks in advance!

hey ralf. dont do copy paste. open the .sql file in SSMS and then run it will work...

Download the attached file and then execute. I think while copying you missed some code. For me it worked properly.

Any idea on how to capture CPU % used by sql server?.
I mean CPU % shown in task manager or process - Processor%time-> perfmon counter and not the CPU cycles shown by sysprocess or @@cpu_busy ( cpu ticks )

Regards,
Raj

http://Strictlysql.blogspot.com