SQL Server Startup

From time to time I see a question asking how to determine the last time a SQL Server was either restarted or when the last time was that the database was used.  For the first question there is a pretty common answer that works for SQL 2000 and up.  In answering both questions though, I am going to show you three quick similar scripts that can be used to find this information.  The catch is that these queries will not work on SQL 2000.  On the flipside, it should give you an idea of what can be done to find out the same info for SQL 2000.

TempDB

[codesyntax lang="tsql"]

[/codesyntax]

A common answer to the question of when was SQL Server last started is to check the Create_Date of the tempdb database.  Since tempdb is recreated every time SQL Server is restarted, it is a pretty accurate timestamp of the server start.  This query was written to work for SQL 2005 and above, but you can still find the create_date of the tempdb database in SQL 2000 by using a different query.

sys.dm_exec_requests

[codesyntax lang="tsql"]

[/codesyntax]

In this query we access one of those Dynamic Management views available in SQL 2005 and up.  In this case, we look at the first process id similar to what one may have done with sysprocesses in sql 2000.  Now is probably a good time to also note something that is happening inside the CTE.  Notice the COALESCE that is being used?  I am taking the Max from those fields that are associated with read type of activities in a database.  This information is being pulled from a DMV called sys.dm_index_usage_stats.  This query will get me the most recent activity for reads and writes in each database (notice the group by).

Default Trace

[codesyntax lang="tsql"]

[/codesyntax]

The last one is to make use of the default trace in SQL 2005, 2008 and 2008 R2.  The default trace records the time of certain events that occur in the database and that includes when the database is starting up.  To find the server startup time in this case, I am checking the EventSequence and IsSystem fields for a value of 1.  As you can see, I also dumped the filepath for the default trace file into a variable and use that in the function to get the data.

When comparing performance of these three options, they pretty much came out even.  Each took its turn performing faster – with regards to time.  However, the default trace method did return a more expensive execution plan every single time.  With how fast these queries run, I’m not real sure that that is very conclusive nor that it would be a heavy concern.  These queries are designed more for the occasional run by the DBA rather than to be executed millions of times a day.  You decide which will be best for you.  I think with the use of the function to get the default trace info, I was mildly surprised that the query performed that well.

Another thing to note is that each of these methods will return a slightly different timestamp.  For instance, the tempdb timestamp gives me 3:21:28 and the sys.dm_exec_requests produces a timestamp of 3:21:36 and the tracefile shows a timestamp of 3:21:24.  I don’t have any hard foundation for why that is – just speculation that seems to make sense.  I will just leave it at that though.

In addition to these methods there is also the option of checking the system event logs to determine the SQL Service startup time.  This information should help you when investigating your databases and server and would be something handy to keep in your toolbox.

5 Comments - Leave a comment
  1. Brad Schulz says:

    Hi Jason…

    Thanks for your post.

    One picky thing though…

    The COALESCE will just weed out the NULLs between last_user_scan, last_user_seek, and last_user_lookup, but it will not find the maximum between the three. For example, if I have an index whose last_user_seek is yesterday but the last_user_scan was 1 minute ago, then COALESCE(last_user_seek,last_user_scan) will return yesterday.

    To get the absolute latest read-based activity, the LastRead column should be calculated doing something (admittedly ugly) like this:

    max(case
    when last_user_seek>=isnull(last_user_scan,’19000101′)
    and last_user_seek>=isnull(last_user_lookup,’19000101′)
    then last_user_seek
    when last_user_scan>=isnull(last_user_seek,’19000101′)
    and last_user_scan>=isnull(last_user_lookup,’19000101′)
    then last_user_scan
    else last_user_lookup
    end)

    –Brad

    • Jason Brimhall says:

      Excellent comment. I figured that I only needed to know one of the values to get a close approximation – and to just pick one in case any were null. And then I used the max since I would have several entries and just wanted to aggregate per database.

      For finding out more precisely when the db was accessed – certainly this method you wrote is more accurate and better to use.

  2. Dugi says:

    Jason, this is really a good post, including all SQL Server versions (2000/2005/2008/2008 R2)! I don’t know if there is something more @ Denali!
    Once again good job, m8!

  3. John says:

    Hi Jason,

    Your post on 3/24 for ways to retrieve the last startup of MSSQL is a big help to me. I’m building a “DatabasesInventory” database that will contain info such as SQL Server version, Edition, instance name and now Last Startup Date.

    Thanks! John

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
March 2011
M T W T F S S
« Feb   Apr »
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, October 31, 2014