You may encounter situations where there is no activity in the database whatsoever, yet the percentage reported by DBCC SQLPERF (LOGSPACE) is significant. This article describes how DBCC SQLPERF (LOGSPACE) calculates the value "Log Space Used (%)".
Let's start with a simple scenario by creating a new database using defaults:
A default log size from CREATE DATABASE is about 0.5 MB. Each log file maintains a minimum overhead this is roughly between 200 KB and 300 KB. SQL Server always reserves a minimum amount of space that is considered "in use" for situations where you must log something but otherwise would not have sufficient space to do so, such as the log records required during an ALTER LOG scenario.
With a default log size of about 0.5 MB, you can observe about 30% to 40% of the log marked as used for this database. Therefore, if you run DBCC SQLPERF (LOGSPACE), you may see that the Log Space Used (%) on the database is about 40%.
If you create a new database with a log of 5 MB, the percentage used is roughly 6%; for a 10 MB database it is 3%, and so on. Further tests reveal that these percentages decrease in a linear fashion:
|Database Log Size||Reported % Used by DBCC SQLPERF(LOGSPACE)|
|1 MB||~33 %|
|2 MB||~16 %|
|5 MB||~6 %|
|10 MB||~3 %|
|20 MB||~1.5 %|
Consider a more realistic scenario where the database is not newly created and the transaction log is in use. Assume that the transaction log currently has four Virtual Log Files, two of which are marked active. Of the two active files, one is completely full (VLF 3), and the other has log records in it but is not completely full (VLF 4). The other two files (VLF 1 and VLF 2) are empty.
When you back up the log, the active part of the log is flushed to disk. One of the virtual log files (VLF 3) is now marked as inactive, whereas the one that was only partially full (VLF 4) remains active. Even though Virtual Log File 4 is backed up, SQL Server does not pad it out with zeros and flush it in order to mark that virtual log file as inactive. Therefore, you can have log space in Virtual Log File 4 that was reported as backed up but is still physically recorded, and that space appears as part of the space used in the DBCC SQLPERF calculation.
The script that follows demonstrates this point. Note that the BACKUP LOG backs up 1055 pages = 8642560 bytes, which in this case is larger than a VLF. So, the active VLF after the backup contains ~3465000 bytes, which when you calculate relative to the overall 20 MB allocated for the log, is around 16.5%. If you add this percentage to the overhead discussed previously, it comes close to the 18% number that is reported by DBCC SQLPERF (LOGSPACE).
USE mastergoDROP DATABASE TestDbgoCREATE DATABASE TestDb ON (name = 'TestDb', filename = 'c:\mssql7\data\TestDb.mdf', size = 10) LOG ON (name = 'TestDblog', filename = 'c:\mssql7\data\TestDb.ldf', size = 20)goEXEC SP_DBOPTION 'TestDb', 'trunc' , FALSEgoBACKUP DATABASE TestDb TO DISK = 'c:\mssql7\backup\TestDb.dmp' WITH INITgoUSE TestDbgoCREATE TABLE t1 (c1 INT, c2 CHAR(8000) not null)goDECLARE @ctr INTSELECT @ctr = 0BEGIN TRANWHILE (@ctr < 1000)BEGIN INSERT t1 VALUES(@ctr, 'x') SELECT @ctr = @ctr + 1ENDCOMMIT TRANgo
Execute the following DBCC to see the current percentage of log used and then back up the log:
DBCC SQLPERF (LOGSPACE)goBACKUP TRAN TestDb TO DISK = 'c:\mssql7\backup\TestDb_log.dmp' WITH INITgo
Execute DBCC SQLPERF to see the difference. The percentage of log space used is about 18%.
DBCC SQLPERF (LOGSPACE)go