Check log size for a period of time.

Question

Post reply

Check log size for a period of time.

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
March 10, 2010 at 5:06 pm

#142057

How can I check log size for a period of time. for example I want to know how much log is growing for one month

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

dbcc sqlperf(logspace)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

If you have correctly sized your log and have the correct recovery model and backup strategy you shouldn't have much, if any, log growth.

In order to find out how much it grew you need to have a baseline/starting size, so you need to have a process that stores the log size at the start of the period and at the end of the period. You can query sys.database_files (works in the context of the current database) like this:

SELECT

name,

( size * 8 ) / 1024.00 AS size_in_mb

FROM

sys.database_files

WHERE

type_desc = 'LOG'

You can also query the Default Trace to get this information like this:

SELECT

LEFT(TE.NAME, CHARINDEX(' ', TE.NAME)-1) AS type,

I.DatabaseID,

I.DatabaseName,

I.FileName,

I.StartTime,

I.EndTime,

CONVERT(DECIMAL(18,2), Duration/1000.00) AS duration_ms,

CONVERT(DECIMAL(18,2), I.Duration/1000.00/1000.00) AS seconds,

I.IntegerData * 8 AS growthKB

FROM

sys.traces T CROSS Apply

sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0

THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

ELSE T.[path]

End, T.max_files) I JOIN

sys.trace_events AS TE ON

I.EventClass = TE.trace_event_id

WHERE

T.is_default = 1 ANd

TE.NAME IN ('Log File Auto Grow', 'Data File Auto Grow')

You can filter on start time and just do log file auto grow. You can add/remove columns as you need and aggregate if you like.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

MannySingh SSChampion Points: 12465 More actions · Answer 3

you can extend the statement as below for all databases and also create a Table to capture this info on regular intervals.

sp_msforeachdb @command1 = 'USE [?]; SELECT db_name() as dbname, name as LogFileName, ( size * 8 ) / 1024.00 AS size_in_mb

FROM sys.database_files WHERE type_desc = ''LOG'''

Maninder
www.dbanation.com