Scenario 1:
A new email just arrived – an alert indicating a deadlock occurred right at the beginning of the work day.
Argh.
But I did previously add a couple of trace flags (1204 and 1222) on all our prod instances that give more deadlock info in the error log. That’ll help. Someday I’ll get around to monitoring deadlocks with extended events…
Let me connect to that server in SSMS and drill down through Management –> SQL Server Error Logs… wait a minute. I’ve searched through Windows event logs before using PowerShell cmdlets, so why am I _clicking_ through a GUI? This just feels wrong.
There’s surely a cmdlet for this… found it!
Not sure why I have multiple copies of commands there – probably
loaded multiple modules or something.
Get-SQLErrorLog from SQL Server PowerShell Extensions (SQLPSX) was exactly what I wanted – a function to return the SQL Server Errorlog. I know the time of the deadlock because of the error message in my inbox – it was 09:08:55 this morning.
Let’s give it a 1 minute window before and after that time:
Get-SqlErrorLog -sqlserver "SQLTACOPS" | `
Where {$_.LogDate -GT '2013-06-24 09:07:55' -AND $_.LogDate -LT '2013-06-24 09:09:55' } | ft -a
Using additional conditions can exclude events such as backups and logins. Pipelining output to the Export-CSV cmdlet creates a file that can be quickly saved and shared.
Get-SqlErrorLog -sqlserver "SQLTACOPS" | `
Where {$_.LogDate -GT '2013-06-24 09:07:55' -AND `
$_.LogDate -LT '2013-06-24 09:09:55' -AND `
$_.ProcessInfo -NE 'Logon' -AND `
$_.ProcessInfo -NE 'Backup'} | `
Export-CSV -NoType 2013-06-24_090855_log_deadlock.csv
Scenario 2:
Two databases filled an entire volume with transaction log entries during index maintenance. The recovery model of the databases was temporarily changed to SIMPLE, logs were shrunk, and the databases were reverted to FULL recovery model. Even though full backups of the databases were created before and after changing the recovery model, this breaks the backup chain for those databases and my boss requested I log this in the server redbook. We cycle the error log each night at midnight, and the the recovery models were changed 2 logs (days) ago. Get-SqlErrorLog has a lognumber parameter, where 0 is the the index of the current log.
Get-SqlErrorLog -lognumber 2 -sqlserver "SQLTACOPS" | `
Where {$_.LogDate -GT '2013-06-22 13:00' -AND `
$_.LogDate -LT '2013-06-23 18:00' -AND `
$_.ProcessInfo -NE 'Logon' -AND `
$_.ProcessInfo -NE 'Backup'} | `
Where {$_.text -LIKE 'Setting database option RECOVERY*' } | ft –a
The output (including the censored database names) was then copied and pasted into the redbook for that server. PowerShell can be used as a tool for quick, accurate documentation.
The effort of clicking through a UI to troubleshoot has very little reuse value. A persisted PowerShell session command line history, transcript, or saved script file can be shared, and used again and again.
A question to ask yourself: If I have to do this two or more times, should I consider investing some time to learn how to access the information/automate the process in PowerShell?
