Checking if your SIMPLE databases need a log backup
- by Fatherjack
Hopefully you have read the blog by William Durkin explaining why your SIMPLE databases need a log backup in some cases. There is a SQL Server bug that means in some cases databases are marked as being in SIMPLE RECOVERY but have a log wait type that shows they are not properly configured. Please read his blog for the full explanation and a great description of how to reproduce the issue.
As part of our (William happens to be my Boss) work to recover our affected databases I wrote this small PowerShell script to quickly check our servers for databases that needed the attention that William details.
cls
$Servers = “Server01″,”Server02″,”etc”,”etc”
foreach($Server in $Servers){
write-host “************” $server “****************”
$server = New-Object Microsoft.sqlserver.management.smo.server $Server
foreach($db in $Server.databases){
$db | where {$_.RecoveryModel -eq “Simple” -and $_.logreusewaitstatus -ne “nothing”} | select name, LogReuseWaitStatus
}
}
If you get any results from this query then you should consult Williams blog for the details on what action you should take. This script does give out false positives if in some circumstances depending on how busy your databases are.
Hopefully this will let you check your servers quickly and if you find any problems you can reference Williams blog to understand what you need to do.
