I have been playing with SQL Server 2008 recently. There are many new features which SQL Server 2008 have. One of the interesting addition to SQL Server 2008 is system table field which records when SQL Server was started. This field has data type as datetime that is why it is precise to 3 milisecond.
Note : This will not work with SQL Server 2005 or earlier version. This works with SQL Server 2008 only.
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info
ResultSet:
sqlserver_start_time
———————–
2008-06-27 20:51:53.317
Reference : Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
In previous versions of SQL Server (I think as low as 7) you could use the following query
select crdate from master..sysdatabases where name=’tempdb’
This works because tempdb is recreated when the server starts. It’s create date should be very close to the time the server started.
If I run the query against the DMV to get the start time of the server on 2008 as well as the query for create time on tempdb, my results are only off by 2 seconds.
You can also get the start date by looking at master.sysprocesses and getting the first connection initiated by sql server:
SELECT login_time FROM sysprocesses WHERE spid = 1