MORE INFORMATION
Let's start with a simple scenario by creating a new database using defaults:
CREATE DATABASE TestDb
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:
|
| 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 master
go
DROP DATABASE TestDb
go
CREATE 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)
go
EXEC SP_DBOPTION 'TestDb', 'trunc' , FALSE
go
BACKUP DATABASE TestDb TO DISK = 'c:\mssql7\backup\TestDb.dmp' WITH INIT
go
USE TestDb
go
CREATE TABLE t1 (c1 INT, c2 CHAR(8000) not null)
go
DECLARE @ctr INT
SELECT @ctr = 0
BEGIN TRAN
WHILE (@ctr < 1000)
BEGIN
INSERT t1 VALUES(@ctr, 'x')
SELECT @ctr = @ctr + 1
END
COMMIT TRAN
go
Execute the following DBCC to see the current percentage of log used and then back up the log:
DBCC SQLPERF (LOGSPACE)
go
BACKUP TRAN TestDb TO DISK = 'c:\mssql7\backup\TestDb_log.dmp' WITH INIT
go
Execute DBCC SQLPERF to see the difference. The percentage of log space used is about 18%.
DBCC SQLPERF (LOGSPACE)
go