To find the when SQL server was rebooted couple of times earlier?

Question

To find the when SQL server was rebooted couple of times earlier?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 18, 2017 at 6:29 am

#324593

Though there are different ways to find when sql server was rebooted couple of times earlier. But is there any query we can use to find the last 5/6 occurrences when sql was restarted?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

The SQL Error Log has what you are looking for
Every time an instance is restarted, the previous log is closed and marked as archived, and the new one is started; the default is ten log files.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Or look in the Windows Event log for the Windows events around shutdown and restart.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Lowell - Tuesday, July 18, 2017 6:45 AM
The SQL Error Log has what you are looking for
Every time an instance is restarted, the previous log is closed and marked as archived, and the new one is started; the default is ten log files.

Thanks Lowell. In this approach the problem is, if someone runs (sp_cycle_errorlog) that also will come here as a new file.

Thanks.

mrrobsewell SSC-Addicted Points: 496 More actions · Answer 4

You can use PowerShell for the machine

Get-EventLog -LogName system | where {$_.eventid -eq 6006}

will show the Event Log Service was stopped

Get-EventLog -LogName system | where {$_.eventid -eq 6005}

shows when the event log service was started

Get-EventLog -LogName system | where {$_.eventid -eq 1074}

will show the process X has initiated a restart/shutdown etc

You can add a -ComputerName paramter if you are querying a remote server

Get-EventLog -ComputerName SERVER -LogName system | where {$_.eventid -eq 6006}

https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Thank you everyone!!

Getting the results back using the Powershell.

Thanks.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

SQL-DBA-01 - Tuesday, July 18, 2017 6:57 AM
Thanks Lowell. In this approach the problem is, if someone runs (sp_cycle_errorlog) that also will come here as a new file.

This is true, but there is no other way (Gail lists one too, but it's also subject to purging) to determine this. Control your logs so that you know when they're being recycled. If you have to have a perfect record of something, then don't rely on built-in processes. Set up your own to capture this information over time to something more persistant.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Grant Fritchey - Tuesday, July 18, 2017 7:22 AM
SQL-DBA-01 - Tuesday, July 18, 2017 6:57 AM
Thanks Lowell. In this approach the problem is, if someone runs (sp_cycle_errorlog) that also will come here as a new file.
This is true, but there is no other way (Gail lists one too, but it's also subject to purging) to determine this. Control your logs so that you know when they're being recycled. If you have to have a perfect record of something, then don't rely on built-in processes. Set up your own to capture this information over time to something more persistant.

Doing this should be pretty straightforward. I'd create a stored procedure to write a row to a table only the DBAs can access. Then create a job that runs when SQL Server starts up to execute the procedure. You can add steps to do anything else you want to do when the instance starts up.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Thanks Grant. However we dont need a complete tracking. Just for few adhoc cases to monitor we needed sometimes the complete log. That helps a lot to write the investigation report. Thanks for the advise anyways.

Thanks.

mrrobsewell SSC-Addicted Points: 496 More actions · Answer 9

Of course if I read the question carefully and see SQL and not Server then what you can do is

$Instance = 'DAVE'
$Server = ''
if($Instance -eq 'MSSQLSERVER' -or $Instance -eq 'DEFAULT'){ $Source = 'MSSQLSERVER' }else{ $Source = "MSSQL*$Instance"}
Get-EventLog -LogName Application -ComnputerName $server | Where-Object {$_.EntryType -eq 'Information' -and $_.Source -like $Source -and $_.Message -like "*Starting up database 'master'*" }

Then this will show when SQL was started by identifying the master database starting from the Application Event Log

https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]