Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  1. Pre-Truncate Backup
  2. truncate and shrink
  3. Future full backup 1
  4. Future full backup 2

Thanks,

share|improve this question
2  
So you're saying you don't need those transactions? –  swasheck Jan 25 '13 at 19:34
1  
No! Don't do that if you want to restore at a point in time from that specific log backup. Please read How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008. I'd say you want to make a normal log backup and then switch to SIMPLE recovery model. And only then shrink the log file. –  Marian Jan 25 '13 at 19:36
    
Let's say if i don't need the transaction before Future full backup 1. But I want to be able to do a restore to point of time in the future. –  gavin Jan 25 '13 at 19:40
    
For a database set to full recovery mode to keep log transactions it needs an initial backup to set the starting lsn of the transaction log chain otherwise it's in pseudo-SIMPLE mode sqlskills.com/blogs/paul/… –  MartinC Jan 27 '13 at 1:24
add comment

3 Answers

up vote 8 down vote accepted

will I be able to do a restore to a point in time between Future full backup 1 and 2?

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:

The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model.

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.

share|improve this answer
add comment

The main issue here is it looks like you're not doing any regular log backups. The first thing I would do is make sure you understand SQL Server's Recovery Models. Primarily, databases in FULL and BULKLOGGED need to have regular log backups taken.

I would recommend that to clean up your log file, first take a log backup:

BACKUP LOG foo TO DISK='<backup file location>'

Once this is complete, you can then safely execute your DBCC SHRINKFILE command to resize the log file appropriately.

Finally, you should schedule regular log backups of your database. There are many tools to do this, either Maintenance Plans or Ola Hallengren's Maintenance Scripts. Also, I would highly recommend reading and understanding Thomas Stringer's link to BOL.

share|improve this answer
    
I have found that I often had to run the BACKUP LOG command a couple of times before DBCC SHRINKFILE actually shrinks the file. –  longneck Jan 25 '13 at 20:39
1  
This is because you can not shrink a log file past active VLFs. Use DBCC LOGINFO to see which VLFs are active. –  Mike Fal Jan 25 '13 at 22:08
add comment

I just went through figuring out SQL backups, and had a very similar question.

To restore to a point-in-time you need:

  • The most recent Full backup from before your point-in-time
  • (Optional) The most recent Differential backup after your Full backup and before your point-in-time
  • All the Transaction Log backups since the Full Backup or Differential Backup that you are restoring from

When you run

Backup log dbname with truncate_only

you are not backing up the log, you are erasing it. So the only restore you can do is the restore from your Full (or Differential) backup.

Mike Fal's answer shows how you can setup Transaction log backups, while Thomas' answer explains the log chain, but I felt the need to post exactly what you needed in order to restore to a point-in-time, since I had the same question last week and was very confused about what exactly I needed in order to do a point-in-time restore.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.