January 20, 2015 at 1:33 pm
Is there a DMV that will give me a rate-of-change on a database, amount of log data generated over a period of time?
I need a quick way of showing this so I can plan on the scheduled frequency of log shipping.
Does anyone know of a way?
Thanks in advance.
January 20, 2015 at 1:42 pm
alanspeckman (1/20/2015)
Is there a DMV that will give me a rate-of-change on a database, amount of log data generated over a period of time?I need a quick way of showing this so I can plan on the scheduled frequency of log shipping.
Does anyone know of a way?
Thanks in advance.
sys.dm_io_virtual_file_stats doesn't retain history, but you can use it to query reads and writes on a file at intervals. Perhaps write a t-sql script that loops 100 times, capturing result into a temp table every minute.
http://msdn.microsoft.com/en-us/library/ms190326.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 20, 2015 at 6:56 pm
If you're in a bind and need some sort of info right away, you can check the default trace for growth records by date:
From https://sqlcan.wordpress.com/2012/05/14/getting-database-datalog-file-growth-from-default-trace/
DECLARE @TraceFileName NVARCHAR(512)
SELECT @TraceFileName = path
FROM sys.traces
WHERE id = 1
-- Total Growth by Date Only
SELECT CASE WHEN EventClass = 92 THEN
'Data File'
ELSE
'Log File'
END AS FileType,
Convert(varchar(8),StartTime,112) AS GrowthDate,
Sum((IntegerData*8)/1024) AS GrowthInMB
FROM sys.fn_trace_gettable(@TraceFileName,default)
WHERE (EventClass = 92) OR (EventClass = 93)
GROUP BY EventClass, Convert(varchar(8),StartTime,112)
ORDER BY GrowthDate
February 23, 2015 at 1:54 pm
This helped me, thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply