T-SQL – Finding SQL Server Service Restart Date-Time

T-SQL – Finding SQL Server Service Restart Date-Time


There are many ways to find out the date & time on which SQL Server service was last restarted.

Method-1: Checking SQL Server Error Log

First time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.


sp_readerrorlog 0,1,‘Copyright (c)’

Method-2: Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.


SELECT sqlserver_start_time FROM
sys.dm_os_sys_info;


Or you can use Using sys.dm_exec_sessions DMV

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

SELECT login_time FROM
sys.dm_exec_sessions

WHERE session_id = 1;


Method-3: Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces

where is_default = 1


Method-4: Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases
WHERE name = ‘tempdb’

Advertisements

3 thoughts on “T-SQL – Finding SQL Server Service Restart Date-Time

  1. There is an easier way to find out the create date of any database, for now lets consider ‘tempdb’:

    sp_helpdb tempdb

    –This is a fairly simple command that gives detailed information about a database and frees you from the overhead of typing a T-sql syntax! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s