DBCC SQLPERF (Transact-SQL):
To increase sql Performance always we need to keep an eye on log File size. DBCC SQLPERF command will get size information about the transaction logs for all databases , so this command will useful in helping to monitor the growth of your transaction logs.
Syntax: DBCC SQLPERF( logspace)
The idea here is to capture and store the growth information at set intervals. This can provide a way to report on how much the logs are growing by and when. Reports are then delivered by an automated process.
1.Create a table to store LogTransctions here i taken table name as LogTransMonitor
CREATE TABLE dbo.LogTransMonitor
LogID INT PRIMARY KEY IDENTITY(1,1),
LogDate DATETIME NOT NULL DEFAULT(GETDATE()),
DatabaseName VARCHAR(100) NOT NULL,
LogSizeMB DECIMAL(18, 2) NOT NULL,
LogSpaceUsed DECIMAL(18, 2) NOT NULL,
[Status] INT NOT NULL
CREATE INDEX IX_LogDate ON LogTransMonitor(LogDate);
2.Insert values into LogTransMonitor table
INSERT INTO LogTransMonitor(DatabaseName, LogSizeMB, LogSpaceUsed, [Status])
EXEC ('DBCC SQLPERF(logspace)')
3.Now run the code and then have a look at the data in your new table and it will look very similar to the output from running DBCC SQLPERF(logspace) to screen
select * from LogTransMonitor
TIP: Create a schedule job and run above step 2 insert query every day in certain time then you can keep track on log file size on every day
Thanks for reading this article if you like this article please share with your Friends.