December 20, 2007 at 4:16 pm
I am trying to create a back up process that included shrinking the transaction log files before the backup runs. I am trying to do this for a number of databases at one, but if one fails I would like the process to rollback. Is that possible with DBCC shrinkfile or is once it is complete, that's it?
example:
create procedure TruncateSQLLogs as
--Database 1
use DB1
backup log DB1 with truncate_only
dbcc shrinkfile (2,truncateonly)
--Database 2
use DB2
backup log DB2 with truncate_only
dbcc shrinkfile (2,truncateonly)
go
If this is executed and either line for DB2 fails, will the dbcc shrinkfile be rolled back for DB1?
December 20, 2007 at 4:38 pm
Hi,
No chance for it. DBCC operations are not really suitable in a transaction. So if there's an error then half of your databases will be shrunk.
BTW, why are you doing backup log with truncate_only??? If you don't need the tranlog backup then set your recovery model to simple. Otherwise, you may miss the records you dispose...
-- Erik http://blog.rollback.hu
December 21, 2007 at 7:52 am
Erik,
Thanks for the quick response.
Maybe I have the order incorrect. The problem is we have a number of databases with transaction logs that can grow into multiple gigabytes in only a couple of days. These are third party applications so we don't have a lot of control over how they system works. While space is not a major concern, we just don't want to get it out of hand.
That being said, would it be better to run the backups and then immediately afterwards running the above process? That way the back has the full log if need be.
Any thoughts?
December 21, 2007 at 9:44 am
The big question is the following: do you backup the transaction log regularly or not? This is a cornerstone at database implementation - if you may need to be able to restore to a point in time, not only the latest possible, or you can't afford losing many hours of data then you should backup the tranlog and save the backups. From this perspective it's irrelevant that this is your homegrown application or an out-of-the-box stuff. I don't want to explain the whole how-do-the-backups-work topic, if you're interested, I can give you some good (and short:)) articles. What you suggested (changing truncate and backup) can be quite confusing, so again: if you need the logs then backup them normally, if you don't need the logs, then change your recovery model to simple.
Bottom line: backup log with truncate_only command is something you never issue in a live environment if you understand what does it do (really no fence, I don't mean to hurt you but this is what I think and I have good reasons for that).
-- Erik http://blog.rollback.hu
December 21, 2007 at 10:43 am
No offense taken, but I am curious over your hesitancy to run backup log with truncate_only.
If we are backing up our transaction logs throughout the day and then running a full backup right after we shrink the transaction log. Is there a problem?
December 21, 2007 at 2:06 pm
Ok, so:
Truncate_only invalidates your transaction log backup sequence
says http://support.microsoft.com/kb/272318. Basically this command says the following: Although I set my database to keep the history of transactions, now please throw it out. It's like you would collect your electricity bills accurately just to ceremoniously put them into the shredder... Anyway, it may make one happy :).
Imagine the following: you save your transaction logs every 15 minutes. Before the backup you do a truncate_only log backup, let's say at 22:08, 8 minutes after the last "standard" log backup. And 5 minutes later, at 22:13, during the full database backup your database crashes. You can recover up to the last transaction log backup, that is 22:00. And you lost the chance to recover up to 22:13 if the log file remained available. Of course after you take a successful full backup, you can live happily. However, if you switched the steps and took the backup first, then truncated the log, you wouldn't be able to use you transaction logs at all (honestly, I'm not sure if you would be even able to backup the log in this case). I hope it makes sense considering the previous thoughts.
Anyway, if you backups the log regularly, why do you want to backup the log here with the TRUNCATE_ONLY clause? Do you really need it?
-- Erik http://blog.rollback.hu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply