DBCC SQLPERF (Transact-SQL)DBCC SQLPERF (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

提供所有数据库的事务日志空间使用情况统计信息。Provides transaction log space usage statistics for all databases. SQL ServerSQL Server 中还可用于重置等待和闩锁统计信息。In SQL ServerSQL Server it can also be used to reset wait and latch statistics.

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008SQL ServerSQL Server)、SQL 数据库SQL Database在某些区域以预览版提供Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through SQL ServerSQL Server), SQL 数据库SQL Database (Preview in some regions)

主题链接图标 TRANSACT-SQL 语法约定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

LOGSPACELOGSPACE
返回事务日志的当前大小和用于每个数据库的日志空间的百分比。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.

重要

有关从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始的事务日志的空间使用情况的详细信息,请参阅本主题的备注部分。For more information about space usage information for the transaction log starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), refer to the Remarks section in this topic.

"sys.dm_os_latch_stats" , CLEAR"sys.dm_os_latch_stats", CLEAR
重置闩锁统计信息。Resets the latch statistics. 有关详细信息,请参阅 sys.dm_os_latch_stats (Transact-SQL)For more information, see sys.dm_os_latch_stats (Transact-SQL). 此选项在 SQL 数据库SQL Database中不可用。This option is not available in SQL 数据库SQL Database.

"sys.dm_os_wait_stats" , CLEAR"sys.dm_os_wait_stats", CLEAR
重置等待统计信息。Resets the wait statistics. 有关详细信息,请参阅 sys.dm_os_wait_stats (Transact-SQL)For more information, see sys.dm_os_wait_stats (Transact-SQL). 此选项在 SQL 数据库SQL Database中不可用。This option is not available in SQL 数据库SQL Database.

WITH NO_INFOMSGSWITH NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。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 NameDatabase Name 数据库名称,为该数据库显示日志统计信息。Name of the database for the log statistics displayed.
日志大小 (MB)Log Size (MB) 分配给日志的当前大小。Current size allocated to the log. 该值始终小于最初为日志空间分配的量,因为数据库引擎Database Engine会保留一小部分磁盘空间,用以存放内部标头信息。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 in use to store transaction log information.
“状态”Status 日志文件的状态。Status of the log file. 始终为 0。Always 0.

注释Remarks

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,使用 sys.dm_db_log_space_usage DMV 而不是 DBCC SQLPERF(LOGSPACE) 来返回每个数据库事务日志的空间使用情况信息。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), 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. 有关详细信息,请参阅事务日志 (SQL Server)SQL Server 事务日志体系结构和管理指南For more information see The Transaction Log (SQL Server) and SQL Server Transaction Log Architecture and Management Guide.

权限Permissions

对于 SQL ServerSQL Server,运行 DBCC SQLPERF(LOGSPACE) 需要对服务器的 VIEW SERVER STATE 权限。On SQL ServerSQL Server to run DBCC SQLPERF(LOGSPACE) requires VIEW SERVER STATE permission on the server. 重置等待和闩锁统计信息需要对服务器的 ALTER SERVER STATE 权限。To reset wait and latch statistics requires ALTER SERVER STATE permission on the server.

对于 SQL 数据库SQL Database 高级层和业务关键层,需要数据库的 VIEW DATABASE STATE 权限。On SQL 数据库SQL Database Premium and Business Critical tiers requires the VIEW DATABASE STATE permission in the database. 对于 SQL 数据库SQL Database 标准层、基本层和常规用途层,需要 SQL 数据库SQL Database 管理员帐户。On SQL 数据库SQL Database Standard, Basic, and General Purpose tiers requires the SQL 数据库SQL Database admin account. 不支持重置等待和闩锁统计信息。Reset wait and latch statistics are not supported.

示例Examples

A.A. 显示所有数据库的日志空间信息Displaying log space information for all databases

下面的示例显示 LOGSPACE 实例中包含的所有数据库的 SQL ServerSQL Server 信息。The following example displays LOGSPACE information for all databases contained in the instance of SQL ServerSQL 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.B. 重置等待统计信息Resetting wait statistics

以下示例为 SQL ServerSQL Server 实例重置等待统计信息。The following example resets the wait statistics for the instance of SQL ServerSQL Server.

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

另请参阅See Also

DBCC (Transact-SQL) DBCC (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL) sys.dm_os_latch_stats (Transact-SQL)
sys.dm_os_wait_stats (Transact-SQL) sys.dm_os_wait_stats (Transact-SQL)
sp_spaceused (Transact-SQL) sp_spaceused (Transact-SQL)
sys.dm_db_log_info (Transact-SQL) sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL) sys.dm_db_log_space_usage (Transact-SQL)
sys.dm_db_log_stats (Transact-SQL)sys.dm_db_log_stats (Transact-SQL)