Chapter 3 using system functions, stored procedures, and DBCCSQLPERF commands to monitor SQLS

This is the last article in this series. How to Use the DBCC command to monitor the use of SQLServer log space. Every database must have transaction logs. Transaction logs record each DML operation and apply it to the SQLServer database. If the recovery mode is FULL and there are often DML operations, the log will grow very fast. Quantity

This is the last article in this series. How to Use the DBCC command to monitor the use of SQLServer log space. Every database must have transaction logs. Transaction logs record each DML operation and apply it to the SQLServer database. If the recovery mode is FULL and there are often DML operations, the log will grow very fast. Quantity

This is the last article in this series. How to Use the DBCC command to monitor the use of SQLServer log space.

Preface:

Each database must have transaction logs. Transaction logs record each DML operation and apply it to the SQLServer database. If the recovery mode is FULL and there are often DML operations, the log will grow very fast. When the recovery mode is simple, logs usually increase when the database is in transaction replication or merge replication. If logs are not backed up frequently and the growth of log files is not limited, it may consume all your hard disk space and cause database downtime. As a DBA, you should often monitor the usage of logs to avoid problems.

As a DBA, one of the important responsibilities is to monitor the size of log files to ensure that the database server is down because the space is not exhausted.

SQLServer provides different DBCC commands for DBA to use. dbcc sqlperf is one of the common commands used to monitor the log size. In this article, we will demonstrate how to use this command. In addition to obtaining log information, it can also be used to reset the wait and lock status.

When dbcc sqlperf is used to monitor log usage, you only need to input one parameter LOGSPACE. But it also accepts other commands.

The dbcc sqlperf ('logspace') command returns three columns: DatabaseName, LogSize (MB), and LogSpaceUsed (%)

Steps:

1. Open SSMS and then open a new query window.

2. Enter the following code in the query window:

USE tempdbGOIF OBJECT_ID ('dbo. # tbl_DBLogSpaceUsage ') is not null begin drop table dbo. # tbl_DBLogSpaceUsage endcreate table dbo. # tbl_DBLogSpaceUsage (DatabaseName NVARCHAR (128), LogSize NVARCHAR (25), LogSpaceUsed NVARCHAR (25), [Status] TINYINT) insert into dbo. # tbl_DBLogSpaceUsage EXEC ('dbcc SQLPERF (LOGSPACE) ') -- Query all results: SELECT DatabaseName, LogSize, LogSpaceUsed, [Status] FROM dbo. # tbl_DBLogSpaceUsageGO -- query the results of a specific database: SELECT DatabaseName, LogSize AS LogSizeInMB, LogSpaceUsed LogspaceUsed_In_Percent, [Status] FROM dbo. # tbl_DBLogSpaceUsageWHERE Databasename = 'adventureworks' GO

Analysis:

In this example, a temporary table is created and dbcc sqlperf is called to insert data into the table. Because DBCC commands need to be executed using EXEC, dynamic SQL is used. Based on the returned results, you can predict whether it is necessary to immediately perform the log backup operation (the log space will not be released without the log Truncation in the full backup ).

Extended information:

This statement can be improved to obtain the summary value. After a period of time, the table stores the historical log usage information. It can be used to analyze the growth of logs.