Find Last Restart of SQL Server

By:   |   Updated: 2022-11-22   |   Comments (14)   |   Related: 1 | 2 | > SQL Server Configurations


Problem

In this tip we look at various methods of how to get the start time for a SQL Server instance.

Solution

Here are a few different ways that you can tell when SQL Server was last started.

Method 1: Find Last Start Time for SQL Server using sys.dm_os_sys_info

This DMV, sys.dm_os_sys_info, contains a column that holds the startup time for SQL Server.

You can run the following command to find the start time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
sys.dm_os_sys_info

Method 2: Find Last Start Time for SQL Server using SQL Server Error Log

If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. Note the highlighted text below "SQL Server is starting". You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time.

sql server error log start up time

Here is an example of a cycled error log from a different SQL Server instance and note the highlighted text. If you see this you will need to look at a previous error log until you find the log that contains the information in the image above.

sql server log initialized

Another message to look for in the SQL Server error log is "This instance of SQL Server has been using a process ID of..." and this will provide the date as follows. This method will be helpful if the error log is recycled. These messages show up around 12AM each day.

sql server log initialized

Note: if you are running the Express edition of SQL Server you cannot read the error logs directly from SSMS, but you can use xp_readerrorlog or navigate to the folder where the log files are stored and use a text editor to read the SQL Server error log files.

Method 3: Find Last Start Time for SQL Server using Dashboard Reports

Another option is to use the SQL Server Dashboard Report. Right click on the server name in SSMS and select Reports > Server Reports > Server Dashboard and you will get a report similar to the following.

sql server dashboard report

Method 4: Find Last Start Time for SQL Server using Windows Event Viewer

Another option is to use Windows Event Viewer to look for the startup time. If you open Windows Event Viewer and filter Event ID 17162 you will find all occurrences that are in the log. You could also include a value for Event Sources (in our case MSSQLSERVER) for the SQL Server instance and you can find all of the startup times that are still in the Event Viewer log.

windows event viewer start time

Here is a partial listing of the startup times for this instance.

windows event viewer sql startup log

Method 5: Find Last Start Time from when TempDB was Created

SELECT LastServerRestart = create_date FROM sys.databases WHERE name = 'tempdb'
tempdb creation sql startup log

Method 6: Query sysprocesses for the First Login or First Batch

Here is another query that you could use to get the information.

SELECT MIN(login_time) as LastServerRestart1, MIN(last_batch) as LastServerRestart2 FROM sys.sysprocesses
last start time from sysprocesses
Next Steps
  • Hopefully this information comes in handy the next time you want to find out when SQL Server was started.
  • I think the DMV is the easiest way.
  • The error log method can be used for pretty much any version of SQL Server.
  • Thanks to all of the suggestions below. If there are others let me know and we can update the article.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-11-22

Comments For This Article




Wednesday, May 1, 2024 - 6:48:40 AM - Robert MacCombe Back To Top (92208)
In terms of minimal typing, I find this to be worth a try, to quickly see the LastBatch of SPID 1 - although not sure if it is 100% reliable...
sp_who2 '1'

Thursday, February 16, 2023 - 9:59:57 AM - Mike Yang Back To Top (90933)
You can also use DOS command like this
systeminfo | find "System Boot Time"

Monday, September 16, 2019 - 4:37:07 PM - Danette Riviello Back To Top (82473)

Very helpful - thanks!  I was about to do something like Method 2 - and decided to check if there were a more straightforward way (Method 1).   I appreciate your sharing this!

Friday, February 1, 2019 - 4:36:45 PM - Molnar Back To Top (78941)

Rather than trying to parse the error log, you can obtain the start time indirectly via tempdb:

Select LastServerRestart = create_date
from sys.databases
where name = 'tempdb'

Tuesday, July 12, 2016 - 11:04:38 AM - Greg Robidoux Back To Top (41873)

Hi Hemant,

Not sure.  I would have suggested the things that you already looked at.

-Greg

Tuesday, July 12, 2016 - 10:57:42 AM - hemant Back To Top (41872)

 

 If some windows user had restarted the sql server from ssms by using sql server authentication (eg. sa) then how we can find out his local windows or domain username?

Event viewer user column says : N/A and sql deafult traces says: sa , but nowhere I couldn't found that windows username or domain username

 

Tuesday, September 16, 2014 - 3:08:57 PM - ASHISH Back To Top (34550)

Greg , I apriciate your help.....Thanks 

Tuesday, November 12, 2013 - 1:03:53 AM - Suresh Back To Top (27462)

Thanks 

Wednesday, December 26, 2012 - 3:50:49 AM - Darshan Back To Top (21133)

Thanks for great this article.

Wednesday, December 28, 2011 - 2:22:10 PM - Roberto Fonseca Back To Top (15458)

Another way (I think) the easiest way (if you cannot access the SQL Server Instance or SSMS) is to check the SQL Server Log/SQL Server Agent Log files. The default location is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

Monday, December 19, 2011 - 10:08:51 AM - E Elhassan Back To Top (15393)

 

I found this article to be very useful as well. One other way to find out last time server was rebooted I was using net stats commandline:

C:\net stats Server

You will get more info about your server but on the very top of the return result you will see something like:

Statistics since mm/dd/yyyy hh:mm AM/PM

 

 

Monday, September 19, 2011 - 9:53:58 AM - Paul Kirk Back To Top (14689)

One other method...

 

Instances running SQL 2005 or later usually write an entry to the SQL Errorlog daily at around midnight:

"The instance of SQL server has been using process ID of xxxxx since <date in local time> <date in UTC>. This is an informational message only; no user action is required."

Monday, September 19, 2011 - 9:24:52 AM - Greg Robidoux Back To Top (14688)

Thanks Paul for these additional items. 

Greg

Monday, September 19, 2011 - 9:08:21 AM - Paul Paiva Back To Top (14687)

Greg, thanks for a great article.

Here's a few more ways to identify the start time of the SQL Server instance:

1)  SELECT create_date FROM sys.databases WHERE name = 'tempdb'

This is because tempdb is always created afresh with every start up of the instance.

2)  SELECT Min(login_time), Min(last_batch) FROM sys.sysprocesses

This works because the login_time and last_batch times of any spid under 51 have the start-up time.

3)  Executing sp_who2 will give essentially the same info as (2).
 
Paul

 

 














get free sql tips
agree to terms