Transaction Log size does not match the size of the data being loaded.

I was working with one of our ISV partners recently and they mentioned some very unusual SQL Server transaction log behavior. The transaction log size did not correspond to the actual size of the data being loaded into the database.

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.

We double checked the recovery model by issuing the following query (which confirmed the database was set to FULL):

select * from sys.databases

We even tried changing the Database to ‘Bulk-Logged’, and then retried with ‘Full Recovery’, and still got the same results! Can you guess why this was happening?

If you guessed that the database never had an initial FULL backup, you would be correct.

The answer is in the SQL Server Books online topic ‘Working with Transaction Log Backups’: https://msdn.microsoft.com/en-us/library/ms190440.aspx ;

‘ 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. Restoring a database by using only file backups can become complex. Therefore, we recommend that you start with a full database backup when you can. Thereafter, backing up the transaction log regularly is necessary. This not only minimizes work-loss exposure but also enables truncation of the transaction log. Typically, the transaction log is truncated after every conventional log backup. ‘

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, the moral of the story is: if you expect to be able to recover your database transactions, you must create a full database backup first, this true for FULL and BULK-LOGGED recovery models. This backup can be created right after the actual database is created; in this particular case the backup would be ‘empty’ because the database contains no data. However, this process creates a ‘marker’ that allows the transaction to be written to the transaction log.