How to Tell When SQL Server Started?

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;

Additional Resources

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