Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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?

Comments

Posted by ThomasLL on 29 April 2010

I usually open the SQL Log or logs until I see the restart.

Thanks for the tip.

Thomas

Posted by Dukagjin Maloku on 29 April 2010

Very nice tip, after that you should go here >>>  www.sqlservercentral.com/.../when-was-sql-server-last-restarted_3F00_.aspx  

and check for the second step!

Posted by Anonymous on 29 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, How to Tell When SQL Server Started? - Joe Webb         [sqlservercentral.com]        on Topsy.com

Posted by rudy komacsar on 4 May 2010

How about:

select rtrim(convert(char(24),@@servername)) +

' - version-' + convert(varchar(9),serverproperty('ProductVersion')) +

' - restarted at-' + convert(char(19),crdate,120)

from sysdatabases

   where name = 'tempdb'

Leave a Comment

Please register or log in to leave a comment.