Recently one of the fellow developer asked me how we can determine when the SQL Server instance started?

Hhmm.. Interesting !! Well, I know only three ways by which we can determine the SQL Server Instance start time pretty accurately (I am sure there are other ways too, if you know any other ways then let us know) .

Three ways to determine the start time of SQL Server Instance are as follows:

  1. By using sys.dm_os_sys_info DMV
  2. By “Date Created” of tempdb database
  3. By “login_time” of spid 1 from sys.sysprocesses

Very often I use sys.dm_os_sys_info to gather the data about the underlying system, but here I am not going into the details. I am going to show the ways to determine the start time of the Instance.

SELECT cpu_count,physical_memory_in_bytes,sqlserver_start_time FROM SYS.DM_OS_SYS_INFO

Second way you can see the start time of the Instance is by right clicking tempdb database and then Properties.

After selecting Properties you can see the Date Created of Tempdb which should be pretty close to the result we got above from the DMV.

Third way we can determine the same is by looking at the login_time of spid 1 from sys.sysprocesses. We will use the following query:

SELECT spid,login_time FROM sys.sysprocesses
WHERE spid=1

So we got the results from the three ways and the timings are pretty close to each other. I generally prefer the details from sys.dm_os_sys_info DMV. Let me know if you know any other ways to determine the same thing.

Till then Keep Learning and Keep Coding !!


Subhro Saha