May 20, 2004 at 4:49 am
I have written a backup and maintenance plan for one of our clients running SQL Server 2000.
Backup and Maintenance Plan:
Category | Time | Task | Frequency | Database Recovery Mode |
Backup | 22:00 - 23:00 | Full Database Backup | Daily | Full |
| 23:00 - 00:00 | Truncate transaction logs | Daily | Full |
| | | | |
Maintenance | 00:00 - 02:00 | Run DBCC REINDEX script | Daily | Simple |
| 02:00 - 02:30 | Update statistics | Daily | Simple |
| 02:30 - 03:00 | Update usage | Daily | Simple |
| 02:00 - 21:00 | Backup transaction logs | Hourly | Full |
The client tested this plan and hit a problem when restoring the transaction log backups to their standby/failover database server. (The backups are simply copied over to the failover server and restored).
The first transaction log backup fails to restore on the failover server, returning the error:
The log in this backup set begins at LSN (xxx), which is too late to apply to the database. An earlier log backup that includes LSN (xxx) can be restored. [SQLSTATE 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
According to Microsoft KB 308267 this is a known bug when switching between full and simple database recovery modes. Switching recovery mode causes a break in the transaction log backup chain. Therefore a restore can not be performed. This issue is apparently fixed in Service Pack 3, which is puzzling me, as our client is using Service Pack 3 (Standard Edition).
There a number of suggested workarounds but I am unsure of the most solution. I have revised my plan to the following and would appreciate any comments as I am not sure if this is going to solve the problem.
Does truncating the transaction logs after a full backup have any impact on this?
The maintenance process is now carried out before the full backup to ensure the full backup includes the reindexing.
Revised Plan:
Category | Time | Task | Frequency | Database Recovery Mode |
Maintenance | 22:00 - 22:30 | Backup transaction logs | Daily | Full |
| 22:30 - 00:00 | Run DBCC REINDEX script | Daily | Simple |
| 00:00 - 00:30 | Update statistics | Daily | Simple |
| 00:30 - 01:00 | Update usage | Daily | Simple |
| 01:00 - 01:30 | Backup transaction logs | Daily | Full |
Backup | | | | |
| 01:30 - 02:30 | Full Database Backup | Daily | Full |
| 02:30 - 03:00 | Truncate transaction logs | Daily | Full |
| | | | |
Backup | 04:00 - 21:00 | Backup transaction logs | Hourly | Full |
Thanks, Steve
May 24, 2004 at 8:00 am
This was removed by the editor as SPAM
May 27, 2004 at 12:07 pm
Truncating the transaction log will certainly impact it. You are deleting transactions from the log without backing them up. It would be like taking a full backup, then tlog backup #1, then #2, and trying to restore #2 without restoring #1.
Further, I'm not sure that you can take a full backup, switch to simple, run your dbcc statements, then switch back to full and take a transaction log backup. I think you should either leave it in full, or take a full backup after running your dbcc's.
I think what I would do if you need to switch to simple, is
1) Take a transaction log backup
2) Switch to simple recovery mode
3) Run your dbcc’s
4) Switch to full recovery mode
5) Take a full backup
6) Start your transaction log backup schedule.
Steve
May 28, 2004 at 2:39 am
Thanks for the reply. I have already ammended my backup and maintenance plan exactly as you have above. The client has implemented it and are no longer getting the error.
Revised Backup and Maintenance Plan:
| Category | Time | Task | Frequency | Database Recovery Mode |
| Maintenance | 22:00 - 22:30 | Backup transaction logs | Daily | Full |
| 22:30 - 00:00 | Run DBCC REINDEX script | Daily | Simple | |
| 00:00 - 00:30 | Update statistics | Daily | Simple | |
| 00:30 - 01:00 | Update usage | Daily | Simple | |
| Backup | 01:30 - 02:30 | Full Database Backup | Daily | Full |
| 03:00 - 21:00 | Backup transaction logs | Hourly | Full |
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply