dbcc-sqlperf-transact-sql.md

title	DBCC SQLPERF (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	01/07/2018
ms.prod	sql-non-specified
ms.prod_service	sql-database
ms.service
ms.component	t-sql|database-console-commands
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	SQLPERF DBCC_SQLPERF_TSQL SQLPERF_TSQL DBCC SQLPERF
dev_langs	TSQL
helpviewer_keywords	statistical information [SQL Server], transaction logs transaction logs [SQL Server], space usage space [SQL Server], transaction logs DBCC SQLPERF statement
ms.assetid	ec9225ce-e20f-4b03-8b3a-7bcad8a649df
caps.latest.revision	43
author	barbkess
ms.author	barbkess
manager	craigg
ms.workload	Active

DBCC SQLPERF (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Provides transaction log space usage statistics for all databases. In [!INCLUDEssNoVersion] it can also be used to reset wait and latch statistics.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through [!INCLUDEssCurrent]), [!INCLUDEsqldbesa] (Preview in some regions)

Transact-SQL Syntax Conventions

Syntax

DBCC SQLPERF   
(  
     [ LOGSPACE ]  
     | [ "sys.dm_os_latch_stats" , CLEAR ]  
     | [ "sys.dm_os_wait_stats" , CLEAR ]  
)   
     [WITH NO_INFOMSGS ]  

Arguments

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 [!INCLUDEssSQL11], 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 [!INCLUDEssSDS].

"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 [!INCLUDEssSDS].

WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.

Result Sets

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.

Remarks

Starting with [!INCLUDEssSQL11], 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.

Permissions

On [!INCLUDEssNoVersion] 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 [!INCLUDEssSDS] Premium and Business Critical tiers requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS] Standard, Basic, and General Purpose tiers requires the [!INCLUDEssSDS] admin account. Reset wait and latch statistics are not supported.

Examples

A. Displaying log space information for all databases

The following example displays LOGSPACE information for all databases contained in the instance of [!INCLUDEssNoVersion].

DBCC SQLPERF(LOGSPACE);  
GO  

[!INCLUDEssResult]

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  

B. Resetting wait statistics

The following example resets the wait statistics for the instance of [!INCLUDEssNoVersion].

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);  

See Also

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)