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 (https://blog.sqlauthority.com)
7 Comments. Leave new
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;
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.
Excellent… Steven Wang
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
— 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
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
Solution for Puzzle 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;
SELECT x.DatabaseName, x.LogDate AS BiginDate, x.EndDate,Size
FROM (
SELECT l.*,t.logDate AS EndDate,t.Logsize – l.Logsize AS Size,DENSE_RANK()OVER(PARTITION BY l.databaseName ORDER BY (t.Logsize – l.Logsize) DESC) AS Seq
FROM @logSpaceUsage l
LEFT OUTER JOIN @logSpaceUsage t
ON l.Id = t.id – 1
) AS x
WHERE x.Seq = 1