Transaction log for SQL Server is too big

Question

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,

Answer 1

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.

Answer 2

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.

Answer 3

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.