Post reply

Tracking SQL Server restarts

  • 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 🙂

  • August 3, 2010 at 6:36 am

    #1201470

    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 🙂

  • August 3, 2010 at 7:08 am

    #1201487

    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

  • August 3, 2010 at 7:30 am

    #1201509

    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.

  • August 3, 2010 at 8:27 am

    #1201578

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 3, 2010 at 9:28 am

    #1201622

    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 🙂

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

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