SQL Server – DBCC SQLPERF to monitor transaction log space

To keep track on database transaction log growth, you can use DBCC SQLPERF command that monitors transaction log space used in the database. Log file size is depends on recovery model of database and backup job.  Frequently running transaction log backup can keep transaction log in good shape.

Sql server has dbcc sqlperf command to track the transaction log size.

DBCC SQLPERF(logspace)

Ouput

dbcc_sql

You can run the schedule job that sent you email with output.

OR

Four columns are there in output, you can capture this data in table on regular basis then generate the report using captured data.

CREATE TABLE dbo.GetlogSpace

(date datetime default getdate(),

dbname varchar(50),

log_size decimal(10,5),

log_used decimal(10,5),

status int

)

Now create a stored procedure to capture the log details using dbcc sqlperf(logspace) command

CREATE PROCEDURE sp_getLogspace

AS

BEGIN

INSERT INTO GetlogSpace(dbName, log_size, log_used, status)

DBCC SQLPERF(logspace)

END

Now schedule a job to run above stored procedure on alternate days to generate capture data and insert into table.

Leave a Reply

  

  

  


7 − = 6