How do I check the last restart time of an SQL server?

 To check the last restart time of a SQL Server instance, you can query the system views or use some system functions. Here are several methods to retrieve this information:

Method 1: Using sys.dm_os_sys_info

You can use the following query to get the last restart time from the sys.dm_os_sys_info dynamic management view:

SELECT sqlserver_start_time 
FROM sys.dm_os_sys_info;

• sqlserver_start_time will give you the timestamp of when the SQL Server instance was last started.

Method 2: Using sys.dm_exec_sessions (for active sessions)

If you want to find the last restart time based on the earliest system session, you can check the session details:

SELECT login_time
FROM sys.dm_exec_sessions
WHERE session_id = 1;  -- Session ID 1 is the system session

• The login_time for session_id = 1 will indicate when the SQL Server instance was started, as this session is created when SQL Server starts.

Method 3: Checking the SQL Server Error Log

You can also check the SQL Server error log for the last restart event. The error log often contains a record of when the SQL Server service was started.

Run the following query:

EXEC xp_readerrorlog 0, 1, N'SQL Server is starting', NULL, NULL, NULL, N'DESC';

• This will search the error log for entries related to SQL Server starting. The log entries will typically include the time the SQL Server instance started.

Method 4: Using sys.dm_exec_requests

Sometimes, if you're looking for an active request that could be running since the SQL Server restart, you can use the sys.dm_exec_requests view to check for long-running processes that started after a restart:

SELECT start_time
FROM sys.dm_exec_requests
WHERE session_id = 1;

This will give you the timestamp for the request initiated by session 1 (the system session), which typically starts after SQL Server restarts.

---

Summary

• Most reliable method: SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
• Alternative methods: Check the system session or error logs for additional confirmation.

Let me know if you need more details or clarification!