DBA sometimes needs to know the last restart time of the SQL Server Instance. It is a piece of important information that can help DBA/Developers to investigate further why and who restart the SQL Server engine. There are multiple ways to get this information. You can find the last restart time of the SQL Server by checking the create date of the tempdb, using the T-SQL query, using the SQL Server dashboard, and reading the SQL Server error logs. Lets get into more details.
Method 1: Using TempDB database Create date
Whenever the SQL Server service restarts, SQL Server creates TempDB database. So, we can find the SQL restart time by checking the TempDB database creation date. You can check the tempdb create date by selecting the TempDB properties in SSMS or by executing the below-mentioned query.
SELECT create_date FROM sys.databases WHERE name = ‘tempdb’
Method 2: Using SQL query
The second method is to query the dynamic management view sys.dm_os_sys_info to get the last restart time of the SQL Server instance. Use the following query in the SQLCMD or in the query editor of SQL Server management studio.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
Method 3: Using SQL Server dashboard report
We can also get the SQL Server startup time by checking on the Server Dashboard report. To check the Server Dashboard report, follow the steps mentioned below.
- Right-click on the SQL Instance
- Select Reports, and then click Standard Reports
- Click Server Dashboard
You can see the SQL startup time in the Server Dashboard as shown in the below screenshot.
Method 4: Using sp_readerrorlog
We can also use the System stored procedure sp_readerrorlog to look for the last restart time of the SQL Server instance. This stored procedure read the SQL Server error log files and searches for a particular text while reading the SQL Server error log files. We can use the sp_readerrorlog to search for the “Copyright (c)” keyword in the current SQL error log file. This keyword is logged in log files each time the SQL Server instance restarts.
Execute sp_readerrorlog 0,1,’Copyright (c)’
Conclusion:
In this article, we shared with you four different ways to check the last restart time of SQL Server. I hope this article helps.