Provides transaction log space usage statistics for all databases. In SQL Server it can also be used to reset wait and latch statistics.
Applies to: SQL Server SQL Server SQL Server 2008 through SQL Server SQL Server 2008 SQL Server 2017 SQL Server SQL Server 2008 SQL Server 2017 SQL Database (Preview in some regions)
Transact-SQL Syntax Conventions
DBCC SQLPERF
(
[ LOGSPACE ]
| [ "sys.dm_os_latch_stats" , CLEAR ]
| [ "sys.dm_os_wait_stats" , CLEAR ]
)
[WITH NO_INFOMSGS ]
LOGSPACE
Returns the current size of the transaction log and the percentage of log space used for each database. Use this information to monitor the amount of space used in a transaction log.
[!IMPORTANT] For more information about space usage information for the transaction log starting with SQL Server 2012 (11.x) refer to the Remarks section in this topic.
“sys.dm_os_latch_stats”, CLEAR
Resets the latch statistics. For more information, see sys.dm_os_latch_stats (Transact-SQL). This option is not available in SQL Database
“sys.dm_os_wait_stats”, CLEAR
Resets the wait statistics. For more information, see sys.dm_os_wait_stats (Transact-SQL). This option is not available in SQL Database
WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.
The following 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) | Current size allocated to the log. This value is always smaller than the amount originally allocated for log space because the [!INCLUDEssDE] reserves a small amount of disk space for internal header information. |
Log Space Used (%) | Percentage of the log file currently in use to store transaction log information. |
Status | Status of the log file. Always 0. |
Starting with SQL Server 2012 (11.x) use the sys.dm_db_log_space_usage DMV instead of DBCC SQLPERF(LOGSPACE)
, to return space usage information for the transaction log per database.
The transaction log records each transaction made in a database. For more information see The Transaction Log (SQL Server) and SQL Server Transaction Log Architecture and Management Guide.
On SQL Server to run DBCC SQLPERF(LOGSPACE)
requires VIEW SERVER STATE
permission on the server. To reset wait and latch statistics requires ALTER SERVER STATE
permission on the server.
On SQL Database Premium and Business Critical tiers requires the VIEW DATABASE STATE
permission in the database. On SQL Database SQL Database Standard, Basic, and General Purpose tiers requires the SQL Database SQL Database SQL Database admin account. Reset wait and latch statistics are not supported.
The following example displays LOGSPACE
information for all databases contained in the instance of SQL Server
Here is the result set.
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
master 3.99219 14.3469 0
tempdb 1.99219 1.64216 0
model 1.0 12.7953 0
msdb 3.99219 17.0132 0
AdventureWorks 19.554688 17.748701 0
The following example resets the wait statistics for the instance of SQL Server
DBCC (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_os_wait_stats (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL)
sys.dm_db_log_stats (Transact-SQL)