Tuesday, January 17, 2012

Determine when SQL Server was started

When ever SQL Server is started or restarted the tempdb gets recreated. So to find out when SQL Server was last started, we can use the the query below.

SELECT created_date FROM sys.databases where [name] = 'tempdb'

There are some other ways to find out when SQL Server is last started. When SQL Server starts all the tasks it initiate entries for their login in the sysprocesses table. We can use their login time in this table to determine when SQL Server was last started.

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

SELECT min(login_time) FROM master.dbo.sysprocesses

SELECT login_time FROM master.dbo.sysprocesses WHERE spid =1

If you are running SQL Server 2005 or higher, you can use a Dynamic Management View to determine the number of milliseconds have been elapsed since SQL Server have been started.

USE master

SELECT TOP 1 sample_ms AS Milli_Seconds_Since_Start
   FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 1000 AS Seconds_Since_Start
  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 60000 AS Minutes_Since_Start

  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 3600000 AS Hours_Since_Start
  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

If you are running SQL Server 2008 or higher, you can also use this query:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

An other way is by using sp_readerrorlog.

EXEC sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is recycled manually.

No comments:

Post a Comment