Monday, September 19, 2011

SQL Server : Query to know the SQL Server last re-start time

Every time you start SQL Server "tempdb" gets re-created, so by seeing the tempdb creation datetime you can determine when SQL Server last started. Below query will return you the same.

SELECT name AS [Database Name], crdate AS [CreatedOn] FROM   sys.sysdatabases WHERE  name = 'tempdb'

Another way to see check the same is the SQL Server system view "dm_os_sys_info", which returns the information about the computer on which SQL Server is installed including the resources available to be utilised by SQL Server.

ms_ticks returns the number of milliseconds since the computer was started while sqlserver_start_time returns the date and time that SQL Server last started.

SELECT DATEADD(SECOND,((-1)*(DOSI.[ms_ticks]/1000)), Getdate()) AS OSStartedOn, DOSI.sqlserver_start_time AS SQLServerStartedOn
FROM   sys.[dm_os_sys_info] DOSI

No comments:

Post a Comment