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

適用於: 是SQL Server 否Azure SQL Database 否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 Server (SQL Server 2008SQL Server 2008 及更新版本)、SQL DatabaseSQL Database (在某些區域中為預覽版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 and later), SQL DatabaseSQL 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 DatabaseSQL Database中無法使用。This option is not available in SQL DatabaseSQL 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 DatabaseSQL Database中無法使用。This option is not available in SQL DatabaseSQL 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 EngineDatabase Engine 會保留少量內部標頭資訊所用的磁碟空間。This value is always smaller than the amount originally allocated for log space because the Database EngineDatabase 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 DatabaseSQL Database 進階和業務關鍵層上需要資料庫中的 VIEW DATABASE STATE 權限。On SQL DatabaseSQL Database Premium and Business Critical tiers requires the VIEW DATABASE STATE permission in the database. SQL DatabaseSQL Database 標準、基本,和一般用途層上需要 SQL DatabaseSQL Database 系統管理員帳戶。On SQL DatabaseSQL Database Standard, Basic, and General Purpose tiers requires the SQL DatabaseSQL 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)