Automating SQL Server Health Check (SQL Server 2005)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ritesh Medhe » Automating SQL Server Health Check (SQL...

131 posts, Page 1 of 14

1 2 3 4 5 »»»

Automating SQL Server Health Check (SQL Server 2005)

Rate Topic

Display Mode

Topic Options

Author

Message

Ritesh Medhe

Posted Wednesday, January 27, 2010 10:17 PM

Old 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 (446 views, 19.22 KB)

Post #854906

Omprakash K Deshpande

Posted Thursday, January 28, 2010 12:22 AM

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

fk_okumu

Posted Thursday, January 28, 2010 12:42 AM

Forum 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

dallas-1069889

Posted Thursday, January 28, 2010 12:48 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 21, 2013 10:26 PM
Points: 12, Visits: 103

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

Ritesh Medhe

Posted Thursday, January 28, 2010 12:50 AM

Old 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

Ritesh Medhe

Posted Thursday, January 28, 2010 12:51 AM

Old 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

Ralf.Hoffmann

Posted Thursday, January 28, 2010 12:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 5:12 AM
Points: 2, Visits: 24

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

Ritesh Medhe

Posted Thursday, January 28, 2010 12:55 AM

Old 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

Omprakash K Deshpande

Posted Thursday, January 28, 2010 12:57 AM

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

arr.nagaraj

Posted Thursday, January 28, 2010 1:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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 »

131 posts, Page 1 of 14

1 2 3 4 5 »»»

Permissions