I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup.
I want to truncate and shrink the log file with the following command
Backup log dbname with truncate_only
go
DBCC shrinkfile('logicalfilename',100)
I want to know after I ran the above command, in the future, will I be able to do a restore to a point in time between Future full backup 1 and 2?
Time Line
- Pre-Truncate Backup
- truncate and shrink
- Future full backup 1
- Future full backup 2
Thanks,
Best Answer
Yes. What you're concerned about is the Log Chain. The log chain starts at a full backup. Therefore at
future full backup 1
you will start a new log chain and with transaction log backups you will be able to restore to point-in-time.BOL reference on Working with Transaction Log Backups
As for your initial method of getting your log truncated, note the following from BOL on BACKUP:
And I'm guessing this goes without saying, but I'll be explicit. In order to not run into this same problem you do need to be doing routine transaction log backups.