Export (0) Print
Expand All

DBCC SQLPERF (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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 2008 through current version), SQL Database V12 (Preview in some regions).

Topic link icon 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. You can use this information to monitor the amount of space used in a transaction log.

"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 Database Engine 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 0.

The transaction log records each transaction made in a database. For more information see The Transaction Log (SQL Server).

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 Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the 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.

DBCC SQLPERF(LOGSPACE);
GO

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 SQLPERF("sys.dm_os_wait_stats",CLEAR);
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft