THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

1001st way to find SQL Server service start time

There're a lot of ways to discover when SQL Server service had been last restarted. That's not a pure curiosity issue since almost all the DMVs and performance counters reset at service restart. So in order to calculate averages (IOs per minute, Waits per minute) etc, one needs to know the starting point. There're a lot of ways to find it. One is to take a look in Error Log (if log hadn't been deleted by numerious DBCC ERRORLOG commands). Another way is to look at sys.dm_exec_requests for start_time or in master..sysprocesses for login_time of system processes like LAZY WRITER. Recently I've found one more way: look in sys.databases for create_date of tempdb database. Since tempdb is re-created from scratch every time SQL Server restarts, it works. BOL reference about create_date column also says:
-- Date the database was created or renamed. For tempdb, this value changes every time the server restarts.

P.S. Results you receive trying different ways can vary - half minute here or there - because service restart doesn't happen in a second. But when you need to measure time since startup, 30 seconds usually won't be serious factor.
Published Saturday, March 1, 2008 4:43 PM by Michael Zilberstein
Filed under:

Comments

No Comments
New Comments to this post are disabled
Privacy Statement