 |
|
SQL Product Highlight
Red Gate Software - SQL Monitor
SQL Server performance monitoring and alerting - SQL Monitor offers an easy entrance to advanced server monitoring with a simple design that's a refreshing change from the status quo. Red Gate have added custom metrics and user roles to the product without spoiling its ease-of-use, to help you answer that timeless question, 'How healthy are your servers?'Learn more!
Find Last Restart of SQL Server
|
By: Greg Robidoux | Read Comments (5) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: 1 | 2 | More |
|
Problem
How can I tell when my SQL Server instance was last started? 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: 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.
Method 2: 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.
Here is an example of a cycled error log 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.
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: 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.
Method 4: 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 your Event Sources (in our case MSSQL$SQL2008) for the SQL Server instance and Event ID 17162 you can find all of the startup times that are still in the Event Viewer log.
Here is a partial listing of the startup times for this instance.
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, but this wasn't introduced until SQL Server 2008. Also, the Dashboard Report is probably new for SQL 2008 as well.
- The error log method can be used for pretty much any version of SQL Server.
Last Update: 9/19/2011
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Monday, September 19, 2011 - 9:08:21 AM - Paul Paiva | Read The Tip |
|
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
|
|
| Monday, September 19, 2011 - 9:24:52 AM - Greg Robidoux | Read The Tip |
|
Thanks Paul for these additional items. Greg |
|
| Monday, September 19, 2011 - 9:53:58 AM - Paul Kirk | Read The Tip |
|
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, December 19, 2011 - 10:08:51 AM - E Elhassan | Read The Tip |
|
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
|
|
| Wednesday, December 28, 2011 - 2:22:10 PM - Roberto Fonseca | Read The Tip |
|
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 |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |