DBCC SQLPERF (Transact-SQL)
Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
.gif "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
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. 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)."sys.dm_os_wait_stats" , CLEAR
Resets the wait statistics. For more information, see sys.dm_os_wait_stats (Transact-SQL).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 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. |
Uwagi
The transaction log records each transaction made in a database. For more information see The Transaction Log (SQL Server).
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);