August 12, 2014 at 12:09 pm
I am trying to find out what time and how much transaction log file grows during a day even a week thru a script. I can track this from data collector set and run for a day or even weeks. But I don't think that's a good practice. Is there any best script or DMV(custom) to run and capture the trend the log growth?
August 13, 2014 at 7:44 am
You can do it by checking backups, like this:
USE msdb
SELECT [database_name] AS [Database] ,
DATEPART(MONTH, [backup_start_date]) AS [Month] ,
AVG([backup_size] / 1024 / 1024) AS [Backup Size MB]
FROM msdb.dbo.backupset
WHERE [type] = 'L'
AND [backup_start_date] >= '2014-01-01'
GROUP BY [database_name] ,
DATEPART(MONTH, [backup_start_date])
ORDER BY DATEPART(MONTH, [backup_start_date]) ,
database_name;
SELECT [database_name] AS [Database] ,
DATEPART(DAY, [backup_start_date]) AS [Day] ,
AVG([backup_size] / 1024 / 1024) AS [Backup Size MB]
FROM msdb.dbo.backupset
WHERE [type] = 'L'
AND [backup_start_date] >= '2014-01-01'
GROUP BY [database_name] ,
DATEPART(DAY, [backup_start_date])
ORDER BY database_name ,
DATEPART(DAY, [backup_start_date]);
SELECT [database_name] AS [Database] ,
DATEPART(WEEK, [backup_start_date]) AS [Week] ,
AVG([backup_size] / 1024 / 1024) AS [Backup Size MB]
FROM msdb.dbo.backupset
WHERE [type] = 'L'
AND [backup_start_date] >= '2014-01-01'
GROUP BY [database_name] ,
DATEPART(WEEK, [backup_start_date])
ORDER BY database_name ,
DATEPART(WEEK, [backup_start_date]);
August 14, 2014 at 8:34 am
Thank you!!!!
August 14, 2014 at 8:49 am
The script that was given by sql driver, shows the size of log backup. While I agree that it has a connection to the log's size, it doesn't show if the log had to grow. If you want to check the log's backup size, then that script is perfect, but if you want to check if the file had to grow, then I think that you'll need to use a different script. The script bellow uses the default trace to check if any file (data or log) had to grow. It checks for event clases 92 (data file auto grow) and 93 (log file auto grow) . Unfortunetly I don't remember where I got this script from, so I can't give credit to the script author.
--Find out details about AutoGrow in your DB
DECLARE @curr_tracefilename VARCHAR(500);
DECLARE @base_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @curr_tracefilename = PATH
FROM sys.traces
WHERE is_default = 1;
SET @curr_tracefilename = reverse(@curr_tracefilename);
SELECT @indx = patindex('%\%', @curr_tracefilename);
SET @curr_tracefilename = reverse(@curr_tracefilename);
SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) - @indx) + '\log.trc';
SELECT ( dense_rank() OVER (ORDER BY StartTime DESC) )%2 AS l1,
CONVERT(INT, EventClass) AS EventClass,
DatabaseName,
Filename,
( Duration / 1000 ) AS Duration,
StartTime,
EndTime,
( IntegerData * 8.0 / 1024 ) AS ChangeInSize
FROM ::fn_trace_gettable(@base_tracefilename, DEFAULT)
WHERE EventClass >= 92
AND EventClass <= 93
AND DatabaseName = db_name()
--AND Filename = 'WriteFileNameHere' --If you need the infor for one file, change the filename. if you want for all files, remark the line
ORDER BY StartTime DESC
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply