April 12, 2006 at 12:57 pm
Will this command wipe out any transaction log data:
sp_msforeachdb "use ?; backup log ? with truncate_only ; dbcc shrinkfile(2,5)"
(my task is to reclaim transaction log space 'after' the Transaction Log backups complete. Our Transaction log drives are filling up and I need to shrink the allocated space!!)
any suggestions welcome - thx in advance.
April 12, 2006 at 1:50 pm
Yes the data in the transaction ogs will b unrecoverable as they are marked committed and it flips the backup marker as well. I would suggest take a full backup as soon as you do this. Or look at your databases and only do the ones with the largest TL's and do a full backup right afterwards. Unfortunately you are going to have to most likely do this and perform a full backup to ensure yu have the space you need.
April 13, 2006 at 6:59 am
No. Not really. When you run that command the log file is CHECKPOINTed. Only COMMITTED transactions are removed. In other words, the only data you are removing from the tlog is that data that is already in the database and can't be 'rolled back'.
You won't be able to use the tlog for a restore. You will need to start a new backup 'chain'. Do a full backup and then start your tlog backups.
-SQLBill
April 13, 2006 at 9:02 am
If this is after your log backups, then it should be a minimal amount of log data lost. It does mess up your backup chain, so you do need a full to get that reset.
April 13, 2006 at 1:45 pm
'Recovery Mode' anybody ??? Ultimately this affects how an individual database reacts to the command listed in the beginning of the post. e.g. 'Simple Recovery' does not require a full backup to maintain database recoverability whereas 'Full Recovery' will require one to perform a full backup to maintain recoverabilty. Also, nobody seemed to mention that the adverse affects that might occur when shrinking the transaction log on system databases like master or maybe tempdb ???
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 13, 2006 at 1:48 pm
Even so the log would never have become this type of issue and I would feel safe to assume if Full or Bul Recovery. If they are in simple then yes there is nothing that could be recovered from the TL anyway as it got marked checkpointed. In this case you are right they should check and be awae of this state of their DB anyway and may have not realized. So thanks for the additional input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply