What
Your database is using the bulk-logged recovery model, and you
have ran some minimally logged operations. The transaction
log file is small, but when you back up the transaction log, the
backup file is large.
Why
When you back up a transaction log that contains minimally
logged operations, you are backing up the transaction log together
with the data pages modified by the minimally logged operation.
You are backing up both the transaction log and parts of the
data files. Hence, the output from the backup would look
something like this:
Processed 72 pages for database 'logtest', file
'logtest' on file 1.
Processed 4 pages for database 'logtest', file 'logtest_log' on
file 1.
BACKUP LOG successfully processed 76 pages in 0.207 seconds
(2.997 MB/sec). |
The first line shows the number of data pages that were backed
up. 72 pages were modified by minimally logged operations,
and hence needs to be backed up together with the transaction
log.
In summary, using the bulk-logged recovery model minimizes the
amount of operations that are fully logged in the transaction log,
but does not reduce the size of transaction log backups.
Document history
8/8/2008 | Initial release. |