Tracking SQL Server restarts

Question

Tracking SQL Server restarts

McSQL

SSCarpal Tunnel

Points: 4936
More actions
August 3, 2010 at 6:33 am

#236137

Hello all,
I'd like to introduce uptime SLA's within our environment for SQL Server.
There are obviously a few different ways of tracking physical server (hardware/OS) uptime, but has anyone used any methods which also incorporates SQL Service uptime?
Are stop/starts captured anywhere within SQL so that it can be a simple case of querying an existing table, or is it likely that I will have to start monitoring events and capturing them from now, with say a trace?
Any advice/examples much appreciated.
Many thanks in advance for any responses 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 1

PS, I run in a multiple-version environment (SQL 2000-2008), so any cross-platform solutions would be ideal, but any suggestions still very welcome 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 2

Ian,

The SQL Logs show each time that SQL started - you could parse that. And assuming a normal shutdown, that also is recorded, so you could parse the logs for that also.

What I would do though is to create a job. For the schedule, use "When SQL Agent starts". Have the job post into a table the datetime. Now you have each time SQL was started. I use a similar method to have the server send me an email every time that it restarts. This can also tell you when a cluster fails over to another node.

Unfortunately, there is no corresponding event when SQL shuts down.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

Or you could use Log Parser to scrape the event log (it's in the Application Log) and put the results into a table. As long as the server itself doesn't crash all normal shutdown events get logged into this log.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Wayne and Jeff have my ideas. Each error log's timestamp has the time SQL restarted, but if you restart too many times, you lose that data.

The event log is the best source (System log, service restart) that I'm aware of. Need something to pull out the data, or track it.

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 5

Thanks all,

I have kind of picked parts of everyone's suggestions, and thrown them together to come up with the solution for this and it has now grown to include event tracking now that I'm importing event logs! 🙂

I found dumpevt.exe for anyone who's interested (http://www.systemtools.com/somarsoft/?somarsoft.com) to import the events into a series of staging tables, and specifically as far as Uptime is concerned, I'm looking at events;

17177 in the Application Log,

6013 in the System Log

and

7036 in the System Log

I have also taken Wayne's suggestion to log to a table and send an email alert when the Agent starts, as this should be a critical alert anyway...

Much appreciated anyway and thanks for taking the time to reply 🙂