SQL Server Transaction Log – Large Size After Backup Issue

backupsql-server-2012transaction-log

I have a database that is on Full recovery mode.

I previously made a full backup, and then on a daily basis add differential backups.

Now I have backed up the transaction log for the first time, which reflected in the greatly increased file size of the .bak file. However the log file in the \DATA directory is still the same size.

I was under the impression that the transaction log file would automatically be truncated on a log backup, as mentioned here and elsewhere.

Previously I asked a question on Stack Overflow (here) where the answer indicated that the log file info was not up-to-date with the actual log on disk. Is that the case with this observation?

If so, how can I get the Windows file to refresh to display up-to-date data?

Best Answer

The transaction log does not automatically shrink because you did a backup. This is actually a good thing because processes actually stop when the log grows. Your log has grown to the current size because you had processes that required the log to grow to that size. These could have been long running units of work that generated a lot of log records (like index maintenance). The log 'truncation' you're referring to means that, after a log backup, log space is available for 'reuse'. Again, this process does not automatically reduce the size of the log. To reduce the log size,, you'd need to 'shrink' the log file. Repeated shrinking of the log is discouraged because it typically just re-grows, which affects performance. If you do 'shrink' the log, you should monitor the growth rate. Here is a helpful article. I also found this article that provides additional information.

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – How large will the Transaction Log backup be

If the logs are full, then yes you will probably be looking at needing the same space to backup them up.

If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).

The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:

put the database into Simple Recovery mode Checkpoint put database back into Full Recovery Take Full backup Start taking log backups.

By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.

As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.

Added 03/06/2013

Sorry, I completely forgot this option as well:

Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

Sql-server – How large will the Transaction Log backup be

Related Question