Wednesday, June 10, 2009

DBCC prevents transaction log backups

Every Sunday at 3 AM I run a batch scheduled job in SQL Server 2005 SP3 to verify the integrity of my database:

alter database chbdev1 set single_user with rollback immediate
alter database chbdev1 set recovery simple
dbcc checkdb ('chbdev1', repair_rebuild)
dbcc shrinkdatabase (chbdev1, 10, truncateonly)
dbcc shrinkfile (pe, 10, truncateonly)
dbcc shrinkfile (pe_log, 10, truncateonly)
waitfor delay '00:00:05'
alter database chbdev1 set recovery full
waitfor delay '00:00:05'
alter database chbdev1 set multi_user

One of these steps is causing SQL Server to forget that it had a backup last night, so when transaction log backups begin later during the work day they fail. The easy solution is to do a backup at the end of the script. But, does this DBCC change the database enough to justify not allowing tran log backups without a full? I wonder if one of the Microsoft developers made this a conscious decision.

Another problem with this is that about once every 6 months a database is left in single-user mode. I don't know why, but I don't let this job run if I can not check the database on Sunday morning. It's never run before vacation.

Lots more to learn about this. Let me know if you see a better way to do this.

No comments:

Post a Comment