Automating SQL Server Health Check (SQL Server 2005)

Question

Automating SQL Server Health Check (SQL Server 2005)

Ritesh Medhe

SSCommitted

Points: 1941
More actions
January 27, 2010 at 10:17 pm

#219986

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.......

Viewing 15 posts - 1 through 15 (of 140 total)

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

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

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

fk_okumu Grasshopper Points: 21 More actions · Answer 2

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?

dallas-1069889 SSC Enthusiast Points: 168 More actions · Answer 3

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.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 4

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.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 5

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 12:51 am

#1109781

there are lots of third party tools...

Ralf.Hoffmann SSC Journeyman Points: 94 More actions · Answer 6

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!

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 7

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

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

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

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

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

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 10

for CPU, you can create alert in performance monitor

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 11

Fantastic article Ritesh, You are a star DBA............

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 12

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 2:15 am

#1109806

Thanks Satnam

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 13

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 2:17 am

#1109808

Satnam did you implement this soln?

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 14

Yes Ritesh, i implemented it,it was fantastic.I loved the idea and the entire presentation which i believe only a good DBA can do...........................