Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Automating SQL Server Health Check (SQL Server 2005) Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 10:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
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.......


  Post Attachments 
modifiedscript.txt (525 views, 19.22 KB)
Post #854906
Posted Thursday, January 28, 2010 12:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 7:24 AM
Points: 435, Visits: 137
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

Post #854956
Posted Thursday, January 28, 2010 12:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 29, 2010 5:39 AM
Points: 1, Visits: 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?
Post #854962
Posted Thursday, January 28, 2010 12:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:41 PM
Points: 12, Visits: 105
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.
Post #854964
Posted Thursday, January 28, 2010 12:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
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.
Post #854966
Posted Thursday, January 28, 2010 12:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
there are lots of third party tools...
Post #854967
Posted Thursday, January 28, 2010 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 2, Visits: 26
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!
Post #854968
Posted Thursday, January 28, 2010 12:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
hey ralf. dont do copy paste. open the .sql file in SSMS and then run it will work...
Post #854970
Posted Thursday, January 28, 2010 12:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 7:24 AM
Points: 435, Visits: 137
Download the attached file and then execute. I think while copying you missed some code. For me it worked properly.
Post #854971
Posted Thursday, January 28, 2010 1:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 PM
Points: 488, Visits: 1,286
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

Strictlysql.blogspot.com
Post #854980
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse