December 1, 2009 at 8:15 pm
We have about 10 instances of SQL Server 2005 spread across several physical servers, and I am looking for a way to automate the checking of the error logs. Ideally, I would like to:
- Run "exec sp_cycle_errorlog;" once a day to keep the error log file sizes manageable
- Search the current error log file for problems once an hour (I'm not sure how to do that--do all errors in the error log start with "Msg "?)
- When an error is found, I would like to generate an email message indicating which instance is having an issue
This is a government site. If possible, I would like to avoid enabling mail (SQL Server or otherwise) on all of our instances so we can avoid any security concerns. I'm thinking we can schedule this recurring task an a non-SQL Server box that already has some sore of command-line based email installed, if we have one. (I'm not sure we have one, so I am open to ideas.) We should be able to use osql.exe to connect to all of the instances from the non-SQL Server box.
Any ideas?
December 1, 2009 at 8:35 pm
What I'd suggest is that you first check to see if you have Windows Event Log monitoring. Sysadmins often have this set up and many of the SQL errors are also posted to the Event Log.
Aside from that, I would search for errors in the log using xp_readerrorlog (http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005) and try to search for the things that matter to you. There are so many possible errors that you might want to start by searching for "Msg" or "Error" or "failed" and then tweak it over time.
I wouldn't cycle logs once a day. If you have issues, you might reboot a server a few times and then you could be losing days of logs. It may or may not be important, but I think once a week keeps sizes very manageable. I suggest you experiment a little on the schedule you want.
In terms of alerts, I'd have each server monitor itself. Store the data in a small db that you keep on each server, and that way you have a good source. If you want to centralize, have one server, or task use osql or SQLCMD to connect to each server and scan that small database for errors. It can then report them to you however makes sense.
December 1, 2009 at 9:36 pm
I did implement the same solution (of course on one central SQL Server) in the current project during last month, and I am in the process of documenting it and will update it here, when done.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 9:38 pm
I did implement the same solution (of course on one central SQL Server) in the current project during last month, and I am in the process of documenting it and will update it here, when done.
Edited : 2nd thread in the last 10 mins or so, dual replies are posted. Browser seems to hang and by the time it is restarted, same perly is posted twice...:hehe::w00t:
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 6:13 pm
Great! Thanks for the link!
December 2, 2009 at 6:14 pm
Thanks, Bru. I am very interested in seeing what you come up with.
December 2, 2009 at 9:46 pm
Look at the link to my personal Blogs, and look at the Blog posted yesterday. That is the starting point, I am using a 5 mins interval at my work place. ( a slight change in the code mentioned in that Blog).
Briefly what I am doing...
Execute such a SQL script in a Job (scheduled at 5 mins interval) and use the Database mail to alert the DBA group about the entries. I am filtering out records older than last 5 minutes.
I would be posting the entire script in a day. Sorry for keeping you waiting.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 6:17 am
Steve,
I created a SQL Agent job that intentionally fails so I can test this, and I tried the following command to read the SQL Agent job:
exec xp_readerrorlog 0, 2
However, I do not see anything for today in the log. Is there a time lag before anything gets written to the SQL Agent log?
J
December 3, 2009 at 7:19 am
Hmmm... Is there any way to check on the health of SQL Server via SNMP? We have WhatsUp Gold Premium v12 installed here, and I don't know if it would be useful or not.
December 3, 2009 at 7:46 am
I don't think that SQL Server publishes SNMP information, but I thought What's Up could monitor some things in SQL Server.
I did find this no Windows SNMP for SQL Server: http://forums.cacti.net/about9459.html&highlight=, but it's for 2000. I haven't seen anyone doing this in SS2K5.
Again, 2000, but search around or contact Ipswitch (and let us know) : http://support.ipswitch.com/kb/WP-20050303-DM03.htm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply