Tuesday, December 28, 2010

 

SQL Server Transaction Log size does not match the size of the data being loaded

I came across this cautionary tale from the SQL Server Development Customer Advisory Team, while looking for information on virtual log files (VLF):

The ISV created an empty database, setup a backup device, set the database recovery model to FULL, and then started loading data. They also intended to backup the transaction log occasionally to keep the log a manageable size. However, at the end of the load process although the database contained 92 GB of data, the log was only 1.2 GB. One would have expected that the transaction log in the case of FULL recovery would have been approximately the same size as the data.

After confirming the database was in FULL recovery mode, they tried BULK LOGGED and got the same result!

The reason? The database had never had an initial FULL backup!

From Books Online ‘Working with Transaction Log Backups’:

‘Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. […]‘

Because there was no initial FULL database backup, the database ‘effectively’ remained in SIMPLE recovery mode; SQL Server was truncating the transaction log automatically.

So, for FULL and BULK-LOGGED recovery models, create a full database backup right after creating a database. (This initial Transaction Log backup will be ‘empty’ because the database contains no data)


# posted by Mitch Wheat @ 9:13 am

1 Comments:

Do you know what the logic behind this is?

As far as I was aware, the log and data backups were completely independent in terms of backup.

There is an obvious dependency during recovery operations... maybe this is why MS designed it as such? To allow a full-recovery database to run in an 'optimised' configuration until it's physically capable of running a full recovery...?

By Anonymous Mark, at February 15, 2011 9:02 am  

Post a Comment



<< Home
    

Powered by Blogger