INF: How DBCC SQLPERF (Logspace) Reports Percentage Used

Article translations Article translations
Article ID: 281879 - View products that this article applies to.
This article was previously published under Q281879
Expand all | Collapse all

SUMMARY

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 (%)".

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:

Collapse this tableExpand this table
Database Log SizeReported % 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 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
				

Properties

Article ID: 281879 - Last Review: August 15, 2001 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbinfo KB281879

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com