Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log File Continues to grow Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 7:28 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:07 AM
Points: 701, Visits: 1,733
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.



Post #1464139
Posted Monday, June 17, 2013 7:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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


---------------------------------------------------------------------

Post #1464149
Posted Monday, June 17, 2013 8:39 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:07 AM
Points: 701, Visits: 1,733
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.



Post #1464184
Posted Monday, June 17, 2013 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:11 PM
Points: 31,018, Visits: 15,455
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
Post #1464189
Posted Monday, June 17, 2013 8:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:07 AM
Points: 701, Visits: 1,733
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.



Post #1464200
Posted Monday, June 17, 2013 9:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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?


---------------------------------------------------------------------

Post #1464213
Posted Monday, June 17, 2013 9:35 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:07 AM
Points: 701, Visits: 1,733
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).



Post #1464218
Posted Monday, June 17, 2013 9:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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.


---------------------------------------------------------------------

Post #1464229
Posted Monday, June 17, 2013 9:53 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:07 AM
Points: 701, Visits: 1,733
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?



Post #1464230
Posted Monday, June 17, 2013 10:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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.

---------------------------------------------------------------------

Post #1464238
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse