SQL SERVER – Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You
- by pinaldave
First of all – if you are going to say this is very old subject, I agree this is very (very) old subject. I believe in earlier time we used to have this only option to monitor Log Space. As new version of SQL Server released we all equipped with DMV, Performance Counters, Extended Events and much more new enhancements. However, during all this year, I have always used DBCC SQLPERF(logspace) to get the details of the logs. It may be because when I started my career I remember this command and it did what I wanted all the time.
Recently I have received interesting question and I thought, I should request your help. However, before I request your help, let us see traditional usage of DBCC SQLPERF(logspace).
Every time I have to get the details of the log I ran following script. Additionally, I liked to store the details of the when the log file snapshot was taken as well so I can go back and know the status log file growth. This gives me a fair estimation when the log file was growing.
CREATE TABLE dbo.logSpaceUsage
(
id INT IDENTITY (1,1),
logDate DATETIME DEFAULT GETDATE(),
databaseName SYSNAME,
logSize DECIMAL(18,5),
logSpaceUsed DECIMAL(18,5),
[status] INT
)
GO
INSERT INTO dbo.logSpaceUsage
(databaseName, logSize, logSpaceUsed, [status])
EXEC ('DBCC SQLPERF(logspace)')
GO
SELECT *
FROM dbo.logSpaceUsage
GO
I used to record the details of log file growth every hour of the day and then we used to plot charts using reporting services (and excel in much earlier times). Well, if you look at the script above it is very simple script. Now here is the puzzle for you.
Puzzle 1: Write a script based on a table which gives you the time period when there was highest growth based on the data stored in the table.
Puzzle 2: Write a script based on a table which gives you the amount of the log file growth from the beginning of the table to the latest recording of the data.
You may have to run above script at some interval to get the various data samples of the log file to answer above puzzles. To make things simple, I am giving you sample script with expected answers listed below for both of the puzzle.
Here is the sample query for puzzle:
-- This is sample query for puzzle
CREATE TABLE dbo.logSpaceUsage
(
id INT IDENTITY (1,1),
logDate DATETIME DEFAULT GETDATE(),
databaseName SYSNAME,
logSize DECIMAL(18,5),
logSpaceUsed DECIMAL(18,5),
[status] INT
)
GO
INSERT INTO dbo.logSpaceUsage
(databaseName, logDate, logSize, logSpaceUsed, [status])
SELECT 'SampleDB1', '2012-07-01 7:00:00.000', 5, 10, 0
UNION ALL
SELECT 'SampleDB1', '2012-07-01 9:00:00.000', 16, 10, 0
UNION ALL
SELECT 'SampleDB1', '2012-07-01 11:00:00.000', 9, 10, 0
UNION ALL
SELECT 'SampleDB1', '2012-07-01 14:00:00.000', 18, 10, 0
UNION ALL
SELECT 'SampleDB3', '2012-06-01 7:00:00.000', 5, 10, 0
UNION ALL
SELECT 'SampleDB3', '2012-06-04 7:00:00.000', 15, 10, 0
UNION ALL
SELECT 'SampleDB3', '2012-06-09 7:00:00.000', 25, 10, 0
GO
Expected Result of Puzzle 1
You will notice that there are two entries for database SampleDB3 as there were two instances of the log file grows with the same value.
Expected Result of Puzzle 2
Well, please a comment with valid answer and I will post valid answers with due credit next week. Not to mention that winners will get a surprise gift from me.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: DBCC
