How to Check SQL Server Instance Start Time in 7 Ways?

In SQL Server, if we want to check the start time of the SQL Server instance, what methods and techniques can we use? Below are some summarized methods for checking the start time of the SQL Server instance.

Method 1. Check  SQL Server instance start time with sys.dm_server_services

By checking the system dynamic management view sys.dm_server_services, you can find a field called last_startup_time which indicates the date and time the service was last started.

SELECT  servicename ,
        process_id ,
        startup_type_desc ,
        status_desc ,
        last_startup_time ,
        service_account ,
        is_clustered ,
        cluster_nodename ,
        [filename]
FROM    sys.dm_server_services WITH ( NOLOCK )
OPTION  ( RECOMPILE );

Method 2. Check SQL Server instance start time based on the creation time of the tempdb database

Every time the SQL Server service starts, it recreates the tempdb database. Therefore, you can determine the SQL Server instance start time based on the creation time of the tempdb database.

--The creation time of the system database tempdb
SELECT CREATE_DATE AS StartDateTime
FROM sys.databases
WHERE NAME='tempdb'

Method 3. Get the SQL Server service start time using master..sysprocesses

By checking the system compatibility view master..sysprocesses, you can get the SQL Server service start time. The session with ID 1 is created when the SQL Server service starts.

SELECT CONVERT(VARCHAR(30), LOGIN_TIME,120) AS StartDateTime
FROM master..sysprocesses WHERE spid=1;

Method 4. Check the SQL Server instance start time with sys.dm_os_sys_info

By checking the DMV sys.dm_os_sys_info, you can find the field sqlserver_start_time, which indicates the date and time when SQL Server was last started.

Method 5. Check the SQL Server instance start time with sys.dm_exec_requests

Check the SQL Server start time by viewing the DMV sys.dm_exec_requests. The session with ID 1 is created when SQL Server starts. Its start_time (the timestamp when the request arrived) can determine the SQL Server service start time.

SELECT start_time AS StartDateTime 
FROM sys.dm_exec_requests WHERE session_id = 1

Method 6. Check the SQL Server instance start time with sys.traces catalog view

This catalog view contains information about the traces currently running in the system.

 

SELECT start_time AS StartDateTime
FROM sys.traces
WHERE is_default=1;

Method 7. Check the SQL Server instance start time with xp_ReadErrorLog

This method is for reference only. It can query the SQL Server instance start time under certain conditions. Unless your error log has not changed since the database instance started, if the error log file is deleted or overwritten, this script will not be able to retrieve the data. However, if the error log from the time SQL Server started is still available, you can also find the SQL Server instance start time from the error log.

use master
go
EXEC xp_ReadErrorLog 0, 1, N'SQL',N'Starting'