Skip to content

dbcc-sqlperf-transact-sql.md

Latest commit

 

History

History
126 lines (91 loc) · 5.7 KB

dbcc-sqlperf-transact-sql.md

File metadata and controls

126 lines (91 loc) · 5.7 KB
title DBCC SQLPERF (Transact-SQL)
description DBCC SQLPERF provides transaction log space usage statistics for all databases.
author rwestMSFT
ms.author randolphwest
ms.date 12/05/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
f1_keywords
SQLPERF
DBCC_SQLPERF_TSQL
SQLPERF_TSQL
DBCC SQLPERF
helpviewer_keywords
statistical information [SQL Server], transaction logs
transaction logs [SQL Server], space usage
space [SQL Server], transaction logs
DBCC SQLPERF statement
dev_langs
TSQL

DBCC SQLPERF (Transact-SQL)

[!INCLUDE SQL Server SQL Database Azure SQL Managed Instance]

Provides transaction log space usage statistics for all databases. Can also be used to reset wait and latch statistics.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE ssSQL11], see the Remarks section in this article.

"sys.dm_os_latch_stats", CLEAR

Resets the latch statistics. For more information, see sys.dm_os_latch_stats.

"sys.dm_os_wait_stats", CLEAR

Resets the wait statistics. For more information, see sys.dm_os_wait_stats.

WITH NO_INFOMSGS

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

Result set

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 [!INCLUDE ssDE] 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 [!INCLUDE ssSQL11], 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 and SQL Server transaction log architecture and management guide.

Permissions

[!INCLUDE ssNoVersion] requires VIEW SERVER STATE permission on the server to run DBCC SQLPERF(LOGSPACE). To reset wait and latch statistics requires ALTER SERVER STATE permission on the server.

[!INCLUDE ssSDS] Premium and Business Critical tiers require the VIEW DATABASE STATE permission in the database. [!INCLUDE ssSDS] Standard, Basic, and General Purpose tiers require the [!INCLUDE ssSDS] admin account.

Examples

A. Display log space information for all databases

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

DBCC SQLPERF (LOGSPACE);
GO

[!INCLUDE ssResult]

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. Reset wait statistics

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

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

Related content