2 out of 2 rated this helpful - Rate this topic

DBCC SQLPERF (Transact-SQL)

SQL Server 2005

Updated: 15 September 2007

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

Topic link icon 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 percent 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.

"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 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 amount is always smaller than the amount originally allocated for log space because the SQL Server 2005 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.

Remarks

The transaction log records each transaction made in a database. For more information, see Introduction to Transaction Logs.

Permissions

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.

Examples

A. Displaying log space information for all databases

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

B. Resetting wait statistics

The following example resets the wait statistics for the instance of SQL Server.

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

Reference

DBCC (Transact-SQL)
sp_spaceused (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
15 September 2007	Changed content: Clarified the definition of LOGSPACE and Log Size (MB).
17 July 2006	New content: Added example B.

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD