Provides statistics about the use of transaction-log space in all databases.
DBCC SQLPERF (LOGSPACE)
The transaction log accumulates information about changes to data in each database. The information returned by DBCC SQLPERF(LOGSPACE) can be used to monitor the amount of space used and indicates when to back up or truncate the transaction log.
DBCC SQLPERF(LOGSPACE) returns this result set (values and entries may vary):
Database Name Log Size (MB) Log Space Used (%) Status
------------------- ------------------------ -------------------- ------
Northwind 0.9921875 36.614174 0
pubs 0.7421875 45.394737 0
msdb 1.4921875 44.535339 0
tempdb 0.4921875 40.874954 0
model 0.7421875 39.86842 0
master 0.9921875 42.962597 0
(6 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This table describes the columns in the result set.
| Column name | Definition |
|---|---|
| Database Name | Name of the database for the log statistics displayed. |
| Log Size (MB) | The actual amount of space available for the log. This amount is smaller than the amount originally allocated for log space because Microsoft® SQL Server™ reserves a small amount of disk space for internal header information. |
| Log Space Used (%) | Percentage of the log file currently occupied with transaction log information. |
| Status | Status of the log file (always contains 0). |
DBCC SQLPERF permissions default to any user.
This example displays LOGSPACE information for all databases currently installed.
DBCC SQLPERF(LOGSPACE)
GO
Here is the result set:
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
pubs 1.99219 4.26471 0
msdb 3.99219 17.0132 0
tempdb 1.99219 1.64216 0
model 1.0 12.7953 0
master 3.99219 14.3469 0
| sp_spaceused | DBCC |