Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log File Continues to grow


Log File Continues to grow

Author
Message
krypto69
krypto69
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 2419
Hi,

With the help I received on this forum, I setup my log file to backup every hour via a maintenance plan - here's the tsql from within the plan:

EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ULTIPRO_WSI'
GO
BACKUP LOG [ULTIPRO_WSI] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ULTIPRO_WSI\ULTIPRO_WSI_backup_201306170925.trn' WITH NOFORMAT, NOINIT, NAME = N'ULTIPRO_WSI_backup_20130617092515', SKIP, REWIND, NOUNLOAD, STATS = 10



My problem is that even tho I am doing hourly backups of the tran log - and nightly full backups - the log file continues to grow and grow.



george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
what does the query

select log_reuse_wait_desc from sys.databases where name = 'your database'

return,

that will tell you what is preventing your log from truncating

---------------------------------------------------------------------
krypto69
krypto69
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 2419
returns:

'NOTHING'

but log file last Monday was 21.3 G

log file this Monday was 33.1 G - after backing up log every hour and full backup every night.



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36062 Visits: 18736
How big are your log backups? Check the size for each hour. If they are small, then you have something preventing the truncation of the log, like replication or mirroring being paused.

If the log file grows, it's because one of these
a) you don't have log backups
b) you have replication or mirroring that are preventing truncation
c) your load is increasing to the point where you need more peak log space.
d) someone manually grew it.

Are you sure you're checking the right database in sys.databases? You are sure that log backups are on the correct database?

There is no magic here, or no unexplained reason why log backups are not clearing the log.

One thing I noted is that you are backing up to the same file. This is easier to script, but if you have issues with the one file, you lose all log backups. Do yourself a favor and get a script that backs up each hour to a different file.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
krypto69
krypto69
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 2419
If the log file grows, it's because one of these
a) you don't have log backups
b) you have replication or mirroring that are preventing truncation
c) your load is increasing to the point where you need more peak log space.
d) someone manually grew it.

Are you sure you're checking the right database in sys.databases? You are sure that log backups are on the correct database?


Answers:

a) I see that the trn files are being created every hour in my backup directory - and named accordingly
b) no replication or mirroring on this server. Mirroring was setup once years ago, but then disabled/turned off
c) the load is the same, but this particular DB is transaction heavy (about 20g a day)
d) only DBA here is me, and I didn't grow it.

I verified the log backups are on the correct DB.

Is it possible that the log growth is simply due to heavy transactions? I know unchecked it grows about 20G a day. With log hourly log backups and nightly fulls it grows about 10G a week.



george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
how big are the log backups? Any of about 30GB hence leading to the log file growth?

If you right click the database, select reports,standard reports,disk usage you can see if there were any automatic file growths for the log.

Whats the growth factor on your log?

---------------------------------------------------------------------
krypto69
krypto69
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 2419
how big are the log backups? Any of about 30GB hence leading to the log file growth?

If you right click the database, select reports,standard reports,disk usage you can see if there were any automatic file growths for the log.

Whats the growth factor on your log?


Log backups are 447 meg (sum of all trn files for one day)

I can't check reports because I'm stuck in 2000 compatibility mode (vendor DB requirement).



george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
447 MB does not tally with your previous information about log activity.

is it the ldf or trn file that is growing?

Scan the default traces for log growth, you can sort by event class, I don't have the query to find them to hand.

Reindexes (normally run over the weekend) are often culpable for log growths.

---------------------------------------------------------------------
krypto69
krypto69
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 2419
Yeah I'm with you. Doesn't add up..

George - would it be helpful if I get the ldf exact size now and then the exact size tomorrow.

and then compare to that to the growth of the backup (trn) files for that same period of time?



george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
I would expect space in the ldf file to be reused so it should not grow. But no harm to have the figures as evidence to confirm growth or not.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search