SQL SERVER – Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You

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)

About these ads

8 thoughts on “SQL SERVER – Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You

  1. Declare @logSpaceUsage TABLE
    (
    id INT IDENTITY (1,1),
    logDate DATETIME DEFAULT GETDATE(),
    databaseName SYSNAME,
    logSize DECIMAL(18,5),
    logSpaceUsed DECIMAL(18,5),
    [status] INT
    );

    INSERT INTO @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;

    /**************************************************
    Solution for Puzzle 1
    SQL Version: SQL Server 2012
    **************************************************/
    ; With CTE1
    As
    (
    Select databaseName, logDate As BeginTime, lead(logDate,1) Over(Partition By databasename order by logdate) As Endtime
    , lead(logSize,1) Over(Partition By databasename order by logdate) – logSize As [MB Growth]
    From @logSpaceUsage
    )
    , CTE2
    As
    (
    Select databaseName, BeginTime, Endtime, [MB Growth]
    , DENSE_RANK() Over(Partition By Databasename Order by [MB Growth] Desc) As RN
    From CTE1
    )

    Select databaseName, BeginTime, Endtime, [MB Growth]
    From CTE2
    Where RN = 1
    Order By databasename, BeginTime;

    /**************************************************
    Solution for Puzzle 1
    SQL Version: SQL Server 2005/08
    **************************************************/
    ; With CTE1
    As
    (
    Select databaseName, logDate, logSize
    , Row_Number() Over(Partition By databasename Order by logdate) As RN
    From @logSpaceUsage
    )
    , CTE2
    As
    (
    Select A.databaseName, A.logDate As BeginTime, B.logDate As EndTime, B.[logSize] – A.logSize As [MB Growth]
    , DENSE_RANK() Over(Partition By A.Databasename Order by B.[logSize] – A.logSize Desc) As RN
    From CTE1 A
    Left Outer Join
    CTE1 B
    On A.databasename = b.databaseName
    And A.RN = B.RN – 1
    )

    Select databaseName, BeginTime, Endtime, [MB Growth]
    From CTE2
    Where RN = 1
    Order By databasename, BeginTime;

    /**************************************************
    Solution for Puzzle 2
    SQL Version: SQL Server 2012
    **************************************************/
    Select Distinct databaseName, First_Value(logDate) Over(Partition By databaseName Order by logDate) As BeginTime
    , Last_Value(logDate) Over(Partition By databaseName Order by logDate Rows Between current row and Unbounded following) As Endtime
    , Last_Value(logSize) Over(Partition By databaseName Order by logDate Rows Between current row and Unbounded following)

    First_Value(logSize) Over(Partition By databaseName Order by logDate) As [Total MB Growth]
    From @logSpaceUsage
    Order By databasename;

    –or
    ; With CTE1
    As
    (
    Select Distinct databaseName, First_Value(logDate) Over(Partition By databaseName Order by logDate) As BeginTime
    , Last_Value(logDate) Over(Partition By databaseName Order by logDate Rows Between current row and Unbounded following) As Endtime
    , Last_Value(logSize) Over(Partition By databaseName Order by logDate Rows Between current row and Unbounded following)

    First_Value(logSize) Over(Partition By databaseName Order by logDate) As [Total MB Growth]
    , Row_Number() Over(Partition By databasename Order by ID) As RN
    From @logSpaceUsage
    )

    Select databaseName, BeginTime, Endtime, [Total MB Growth]
    From CTE1
    Where RN = 1
    Order By databasename
    /**************************************************
    Solution for Puzzle 2
    SQL Version: SQL Server 2005/08
    **************************************************/
    ; With CTE1
    As
    (
    Select databaseName, logDate, logSize
    , Row_Number() Over(Partition By databasename Order by logdate) As RN_Asc
    , Row_Number() Over(Partition By databasename Order by logdate Desc) As RN_Desc
    From @logSpaceUsage
    )

    Select A.databaseName, A.logDate As BeginTime, B.logDate As EndTime, B.[logSize] – A.logSize As [Total MB Growth]
    From CTE1 A
    Inner Join
    CTE1 B
    On A.databasename = b.databaseName
    And A.RN_Asc = B.RN_Desc
    Where A.RN_Asc = 1
    Order By databasename;

  2. Sir,
    i have a problem in “pivot”.
    i am using dynamic columns in pivot query and it gives result properly
    but if any column does not have any value then it return “Null”
    and i want 0(Zero) in place of “Null”.
    so, could you please help me?
    Thnaks.

  3. Select Distinct ROW_NUMBER() OVER(Order by (Select 0)) AS Row_id,
    A.databaseName as DNAme,A.logDate as BeginDATE,B.logDate as ENDDATE,A.logsize
    INTO #TEMP
    FROM logSpaceUsage A JOIN logspaceusage B
    ON A.databaseName = B.databaseName
    WHERE B.logDate IN (Select top 1 B.logDate
    FROM logSpaceUsage A JOIN logspaceusage B
    ON A.databaseName = B.databaseName
    Order by logdate desc)
    group by A.databaseName,A.logDate,B.logDate

    SELECT RANK() OVER ( ORDER BY A.[DName] DESC) AS GroupID,
    A.[row_id], A.[DName], A.[begindate], A.[Enddate],A.logsize – ISNULL(B.[logsize], 0) AS Score
    INTO #TEMP1
    FROM (SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [DName] ORDER BY [DName], [row_id]) AS Row
    FROM #temp) A
    LEFT JOIN (SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [DName] ORDER BY [DName], [row_id]) AS Row
    FROM #temp) B ON B.[DName] = A.[DName]
    AND A.Row = B.Row + 1

    –Script For Puzzle 2
    With CTE
    As
    (
    select Row_Number() Over(Partition by A.groupid Order By A.groupid asc) As Row_Num
    , B.groupid,B.DNAME,B.begindate,B.Enddate,A.LogSize
    From (SELECT (SUM(score)-MAX(score) )AS LogSize,groupid,dname FROM #temp1 Group By Dname,groupid)as A
    inner join
    #temp1 B
    on A.groupid = B.groupid and A.dname = B.dname
    )

    Select DNAME,begindate,Enddate,logsize
    From CTE
    Where ROW_NUM = 1

  4. – Script for puzzle 1
    Select row_number() over (order by (Select 0)) AS ID,A.databaseName,A.logDate,A.logSize
    into #temp
    from logSpaceUsage A JOIN logSpaceUsage B
    ON A.databaseName = B.databaseName and A.logDate B.logDate
    where A.logDate < B.logDate
    group by A.databaseName,A.logDate,A.logSize

    select
    x.id xId,
    x.databasename as name,
    x.logdate as BeginDate,
    y.logdate as EndDate,
    x.logsize logsizeA,
    y.logsize logsizeB,
    (x.logsize – y.logsize) MBGrowth
    into #temp2
    from
    #temp x
    left join #temp y on x.id + 1 = y.id
    where x.logdate (Select max(logdate)from #temp)
    order by x.id

    SELECT tt.name,tt.begindate,tt.enddate,tt.Mbgrowth
    FROM #temp2 tt
    INNER JOIN
    (
    SELECT name, MAX(MBGrowth) AS Maxgrowth
    FROM #temp2
    GROUP BY name
    ) groupedtt ON tt.name = groupedtt.name AND tt.mbgrowth = groupedtt.Maxgrowth
    order by xid

  5. Sorry…Some mistakes in that query..
    Pls chk the below one.

    Select Distinct ROW_NUMBER() OVER(Order by (Select 0)) AS Row_id,
    A.databaseName as DNAme,A.logDate as BeginDATE,B.logDate as ENDDATE,A.logsize
    INTO #TEMP
    FROM logSpaceUsage A JOIN logspaceusage B
    ON A.databaseName = B.databaseName
    WHERE B.logDate IN (Select top 1 B.logDate
    FROM logSpaceUsage A JOIN logspaceusage B
    ON A.databaseName = B.databaseName
    Order by logdate desc)
    group by A.databaseName,A.logDate,B.logDate,A.logSize

    SELECT RANK() OVER ( ORDER BY A.[DName] DESC) AS GroupID,
    A.[row_id], A.[DName], A.[begindate], A.[Enddate],A.logsize – ISNULL(B.[logsize], 0) AS Score
    INTO #TEMP1
    FROM (SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [DName] ORDER BY [DName], [row_id]) AS Row
    FROM #temp) A
    LEFT JOIN (SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [DName] ORDER BY [DName], [row_id]) AS Row
    FROM #temp) B ON B.[DName] = A.[DName]
    AND A.Row = B.Row + 1

    –Script For Puzzle 2
    With CTE
    As
    (
    select Row_Number() Over(Partition by A.groupid Order By A.groupid asc) As Row_Num
    , B.groupid,B.DNAME,B.begindate,B.Enddate,A.LogSize
    From (SELECT (SUM(score)-MAX(score) )AS LogSize,groupid,dname FROM #temp1 Group By Dname,groupid)as A
    inner join
    #temp1 B
    on A.groupid = B.groupid and A.dname = B.dname
    )

    Select DNAME,begindate,Enddate,logsize
    From CTE
    Where ROW_NUM = 1

    — Script for puzzle 1
    Select row_number() over (order by (Select 0)) AS ID,A.databaseName,A.logDate,A.logSize
    into #temp2
    from logSpaceUsage A JOIN logSpaceUsage B
    ON A.databaseName = B.databaseName and A.logDate B.logDate
    where A.logDate < B.logDate
    group by A.databaseName,A.logDate,A.logSize
    –Select * from #temp2
    select
    x.id xId,
    x.databasename as name,
    x.logdate as BeginDate,
    y.logdate as EndDate,
    x.logsize logsizeA,
    y.logsize logsizeB,
    (x.logsize – y.logsize) MBGrowth
    into #temp3
    from
    #temp2 x
    left join #temp2 y on x.id + 1 = y.id
    where x.logdate (Select max(logdate)from #temp2)
    order by xid

    SELECT tt.name,tt.begindate,tt.enddate,tt.Mbgrowth
    FROM #temp3 tt
    INNER JOIN
    (
    SELECT name, MAX(MBGrowth) AS Maxgrowth
    FROM #temp3
    GROUP BY name
    ) groupedtt ON tt.name = groupedtt.name AND tt.mbgrowth = groupedtt.Maxgrowth
    order by xid

    drop table #temp
    drop table #TEMP1
    drop table #temp2
    drop table #temp3

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s