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 01, 2008 4:43 PM by Michael Zilberstein
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement