Yet another late night/early morning tale...
The LOG backups for multiple databases on a server failed overnight (not all databases, but many of them), and looking in the output file from the LiteSpeed Maintenance plan (the client's tool of choice) I found entries like this:
Database Database01: Transaction Log Backup...
Destination: "I:\MSSQL\Transaction_Log_Backups\Database01\Database01_tlog_201511211012.TRN"
SQL Litespeed™ Version 4.8.4.00086
Copyright (C) 2004-2006, Quest Software Inc.
Quest Software.
Registered Name: ServerA
Msg 62309, Level 19, State 1, Line 0
SQL Server has returned a failure message to LiteSpeed which has prevented the operation from succeeding.
The following message is not a LiteSpeed message. Please refer to SQL Server books online or Microsoft technical support for a solution:
BACKUP LOG is terminating abnormally.
BACKUP LOG cannot be performed because there is no current database backup.
I checked the FULL backup job, and it had completed normally that night.
Looking back in the SQL Server Error Log I saw that the problems started at or around 9 pm local server time the previous night – when I looked at that time I found paired messages like these for *all* of the databases - not just the offending ones:
Date 11/20/2015 9:08:18 PM
Log SQL Server (Current - 11/21/2015 10:16:00 AM)
Source spid110
Message Setting database option RECOVERY to SIMPLE for database Database01
--
Date 11/20/2015 9:08:22 PM
Log SQL Server (Current - 11/21/2015 10:16:00 AM)
Source spid111
Message Setting database option RECOVERY to FULL for database Database01.
These messages showed that a process of some kind ran just after 9 pm that switched the databases from FULL recovery to SIMPLE and then back again. This broke the LOG recovery chain and required new FULL backups before any LOG backups could succeed, which is why the LOG backup job was failing.
The regular nightly FULL backup job on this server runs at 6 pm and normally takes 3-4 hours to complete (that night it took 4.25) – the databases that had backups completed prior to the FULL>>SIMPLE>>FULL switch were those that were failing – the databases that didn’t have backups until after the switch were still OK because they had a FULL *after* the switch to re-start their LOG recovery chain.
The fix in this case was to run new FULL backups of the impacted databases, which thankfully I was able to do without much impact during the morning. (Although DIFF backups could have worked too, as discussed here by Paul Randal (blog/@PaulRandal)
I never could get the users to admit who had made a change (although mysteriously it hasn't happened again :))
--
The cautionary tale part of this relates to the recovery switch. It is not uncommon to see recommendations to switch the recovery model prior to a large data load, or even a nightly import/export process, as part of transaction LOG management (although depending on your operations, a switch to BULK_LOGGED can be just as effective without breaking the LOG chain - there are point-in-time recovery issues for times *during* any actual minimally logged operations). I am not a fan of switching like this, but it is out there.
The takeaway is that you need to plan your backup/restore schedule accordingly - if you use a recovery model switch mechanism make sure to schedule your FULL backups *after* the recovery switch so that this problem doesn't bite you.
Also - always scan vendor-provided scripts for recovery model changes - we all know it is good practice to backup databases before and after major changes (we know that, right?) but it also often doesn't happen for one reason or another. If a script changes the recovery model to SIMPLE and back, you will have this problem if you don't take a new FULL or DIFF backup after the script runs.
Hope this helps!
The LOG backups for multiple databases on a server failed overnight (not all databases, but many of them), and looking in the output file from the LiteSpeed Maintenance plan (the client's tool of choice) I found entries like this:
Database Database01: Transaction Log Backup...
Destination: "I:\MSSQL\Transaction_Log_Backups\Database01\Database01_tlog_201511211012.TRN"
SQL Litespeed™ Version 4.8.4.00086
Copyright (C) 2004-2006, Quest Software Inc.
Quest Software.
Registered Name: ServerA
Msg 62309, Level 19, State 1, Line 0
SQL Server has returned a failure message to LiteSpeed which has prevented the operation from succeeding.
The following message is not a LiteSpeed message. Please refer to SQL Server books online or Microsoft technical support for a solution:
BACKUP LOG is terminating abnormally.
BACKUP LOG cannot be performed because there is no current database backup.
I checked the FULL backup job, and it had completed normally that night.
Looking back in the SQL Server Error Log I saw that the problems started at or around 9 pm local server time the previous night – when I looked at that time I found paired messages like these for *all* of the databases - not just the offending ones:
Date 11/20/2015 9:08:18 PM
Log SQL Server (Current - 11/21/2015 10:16:00 AM)
Source spid110
Message Setting database option RECOVERY to SIMPLE for database Database01
--
Date 11/20/2015 9:08:22 PM
Log SQL Server (Current - 11/21/2015 10:16:00 AM)
Source spid111
Message Setting database option RECOVERY to FULL for database Database01.
These messages showed that a process of some kind ran just after 9 pm that switched the databases from FULL recovery to SIMPLE and then back again. This broke the LOG recovery chain and required new FULL backups before any LOG backups could succeed, which is why the LOG backup job was failing.
The regular nightly FULL backup job on this server runs at 6 pm and normally takes 3-4 hours to complete (that night it took 4.25) – the databases that had backups completed prior to the FULL>>SIMPLE>>FULL switch were those that were failing – the databases that didn’t have backups until after the switch were still OK because they had a FULL *after* the switch to re-start their LOG recovery chain.
The fix in this case was to run new FULL backups of the impacted databases, which thankfully I was able to do without much impact during the morning. (Although DIFF backups could have worked too, as discussed here by Paul Randal (blog/@PaulRandal)
I never could get the users to admit who had made a change (although mysteriously it hasn't happened again :))
--
The cautionary tale part of this relates to the recovery switch. It is not uncommon to see recommendations to switch the recovery model prior to a large data load, or even a nightly import/export process, as part of transaction LOG management (although depending on your operations, a switch to BULK_LOGGED can be just as effective without breaking the LOG chain - there are point-in-time recovery issues for times *during* any actual minimally logged operations). I am not a fan of switching like this, but it is out there.
The takeaway is that you need to plan your backup/restore schedule accordingly - if you use a recovery model switch mechanism make sure to schedule your FULL backups *after* the recovery switch so that this problem doesn't bite you.
Also - always scan vendor-provided scripts for recovery model changes - we all know it is good practice to backup databases before and after major changes (we know that, right?) but it also often doesn't happen for one reason or another. If a script changes the recovery model to SIMPLE and back, you will have this problem if you don't take a new FULL or DIFF backup after the script runs.
Hope this helps!
No comments:
Post a Comment