Sql-server – Transaction log for SQL Server is too big

backuprestoresql serversql-server-2008

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,

Best Answer

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.