How to Tell When SQL Server Started?

finger_trap_2010_04_28.jpg

As a consultant, I’m regularly asked to diagnose and solve problems when I have incomplete or conflicting information given to me by the client. They don’t mean any harm, but different people in the organization have different perspectives and different memories. Sometimes it is difficult to know what is really happening.

When I’m working with systems like SQL Server, it is always good to gather as much information from the system itself as I can and not rely too heavily on someone else’s memory. Trust but verify as President Ronald Reagan once said.

When was the server restarted?

SQL Server maintains a lot of information about how it’s doing and when certain events ocurred. Much of this information resides in the Dynamic Management Views (DMVs) of SQL Server 2005 and 2008.

For example, let’s say we want to know when the SQL Server instance was last restarted. When can easily collect this information using a quick query. There are several other methods as well but this one is my preferred way.

SELECT
Login_Time
FROM
sys.dm_exec_sessions
WHERE
Session_Id = 1;

server_restart_dmv.jpg

Additional Resources

SQL Server Books Online has more information about this and other DMVs. Additionally there are some other great resources on the web.

And there are many, many more. Just use your favorite search engine and begin using the DMVs to make your job a little easier.

Question:

  • What’s your favorite DMV?
About these ads

11 Responses to How to Tell When SQL Server Started?

  1. Glenn Berry says:

    I also like this query:

    SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

    It only works on SQL Server 2008 or above.

  2. Sankar Reddy says:

    I have been using this query. Almost the same time.
    SELECT create_date from sys.databases where name = ‘tempdb’

  3. Joe says:

    Hi Sankar – Yes, I like that one as well since tempdb is recreated each time the service starts.

  4. Joe says:

    Thanks for sharing the new one, Glenn! I’ve got some catching up to do on my blog reading, but the ones I’ve read from your DMV series have been great.

  5. Pingback: Dew Drop – April 29, 2010 | Alvin Ashcraft's Morning Dew

  6. Jack Corbett says:

    On 2005/2008 you can also do

    select
    start_time
    from
    sys.traces
    where
    is_default = 1

  7. Sankar Reddy says:

    Jack,

    Its a neat trick but what happens if the default trace is disabled? I am NOT recommending to disable default trace but being on the forums there are quite a few ppl who disable the default trace.

  8. Pingback: Tweets that mention How to Tell When SQL Server Started? « WebbTech Solutions -- Topsy.com

  9. Pingback: What We’re Reading: May 7th 2010 | Brent Ozar - Too Much Information

  10. I usually do not drop a comment, but after browsing a few of the remarks
    on How to Tell When SQL Server Started? | WebbTech
    Solutions. I do have some questions for you if it’s okay. Is it simply me or do a few of these remarks appear like they are written by brain dead visitors? :-P And, if you are writing on other online social sites, I’d like
    to follow anything fresh you have to post. Would you make a list of
    every one of all your social community pages like your Facebook page, twitter feed, or linkedin profile?

  11. I am truly grateful to the holder of this web site who has shared this fantastic piece of writing at at this time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers