Chapter III-Monitoring SQL Server using system functions, stored procedures, and DBCC SQLPERF commands (3)

Original: Chapter III-Monitoring SQL Server using system functions, stored procedures, and DBCC SQLPERF commands (3)

This is the last article in this series. How the DBCC command will be used to monitor the usage of SQL Server log space.

Objective:

Each database must have a transaction log. The transaction log records each DML operation and is applied to the SQL Server database, and the log grows very fast if the recovery model is full and there are frequent DML operations. When the recovery mode is simple, the log usually grows when the database is in transactional replication or merge replication. If the log is not backed up frequently and the log file growth is not limited, it is possible to consume all your hard disk space and then cause the database to go down. As a DBA, you should always monitor the usage of logs to avoid problems.

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

SQL Server provides different DBCC commands for use by DBAs , where DBCC SQLPERF is one of the common commands used to monitor log sizes, and this command is demonstrated in this article. In addition to getting the log information, you can also use to reset the wait and latch status.

When DBCC SQLPERF is used to monitor log usage, only one parameter logspaceneeds to be passed in. But it also accepts other commands.

DBCC SQLPERF (' logspace ') command returns three columns:DatabaseName,logsize (MB),logspaceused (%)

Steps:

1. Open SSMS and open a new query window.

2. Enter the following code in the query window:

Use tempdbgoif object_id (' dbo. #tbl_DBLogSpaceUsage ') are not NULL     BEGIN        DROP TABLE dbo. #tbl_DBLogSpaceUsage    endcreate TABLE dbo. #tbl_DBLogSpaceUsage    (      DatabaseName NVARCHAR (+),      logsize NVARCHAR (+),      logspaceused NVARCHAR (+),      [Status] TINYINT    ) INSERT into  dbo #tbl_DBLogSpaceUsage        EXEC (' DBCC SQLPERF (logspace) '            )--Query all results: SELECT  DatabaseName,        logsize,        logspaceused,        [Status]from    dbo. #tbl_ dblogspaceusagego--query results for 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 the DBCC SQLPERF is called to insert the data into the table. Because the DBCC command needs to be executed with EXEC , it is implemented using dynamic SQL . Depending on the results returned, it is possible to predict whether it is necessary to perform a log backup operation immediately (the full backup does not truncate the log and does not release the log space).  

Extended information:

This statement can be improved to get summary values, and after a period of time, the table holds the history of log usage information. can be used to analyze the growth of logs.

Chapter III-Monitoring SQL Server using system functions, stored procedures, and DBCC SQLPERF commands (3)