June 17, 2013 at 7:28 am
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.
June 17, 2013 at 7:50 am
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
---------------------------------------------------------------------
June 17, 2013 at 8:39 am
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.
June 17, 2013 at 8:46 am
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.
June 17, 2013 at 8:55 am
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.
June 17, 2013 at 9:30 am
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?
---------------------------------------------------------------------
June 17, 2013 at 9:35 am
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).
June 17, 2013 at 9:47 am
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.
---------------------------------------------------------------------
June 17, 2013 at 9:53 am
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?
June 17, 2013 at 10:05 am
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.
---------------------------------------------------------------------
June 17, 2013 at 10:09 am
you say mirroring was turned off or disabled, but that doesn't mean it was done correctly. If it's not removed from the source, and you don't have a destination, the log will continue to grow.
When you query sys.databases, what is returned for the log_reuse_wait_desc? It should be a number.
If this is 0, then either you have an open transaction, or you are not running log backups as you think you are. Double check everything is correctly configured.
you could try switching to simple mode, then back to full, then take a full backup and shrink the log (DBCC SHRINKFILE). then monitor to see what happens.
June 17, 2013 at 12:32 pm
you say mirroring was turned off or disabled, but that doesn't mean it was done correctly. If it's not removed from the source, and you don't have a destination, the log will continue to grow.
When you query sys.databases, what is returned for the log_reuse_wait_desc? It should be a number.
If this is 0, then either you have an open transaction, or you are not running log backups as you think you are. Double check everything is correctly configured.
you could try switching to simple mode, then back to full, then take a full backup and shrink the log (DBCC SHRINKFILE). then monitor to see what happens.
I know that I disabled mirroring about two years ago. I know that both the source and destination do not show mirroring enabled. But, is there another way to verify?
When I run the query :
select log_reuse_wait_desc from sys.databases where name = 'Ultipro_WSI'
Results do not include a number just: LOG_BACKUP
I'll try switching from simple to full right now and then shrink.
June 17, 2013 at 12:34 pm
..just a follow up..
I was able to shrink the log successfully.
June 18, 2013 at 3:29 am
Hi
I had a similiar issue with an OLTP db on one of my servers, all the symptoms look to be the same. My suggestion is to investigate if the transactions within your log file are being committed or hardened to your data file. The problem I found was that across tran log backups the uncommitted transactions were remaining. The resolution was then to weekly run independantly a TRUNCATE LOG SQL Agent Job, ensuring that uncomitted transactions were rolled back.
Regards,
Ronan
June 18, 2013 at 4:25 am
ronang (6/18/2013)
HiI had a similiar issue with an OLTP db on one of my servers, all the symptoms look to be the same. My suggestion is to investigate if the transactions within your log file are being committed or hardened to your data file. The problem I found was that across tran log backups the uncommitted transactions were remaining. The resolution was then to weekly run independantly a TRUNCATE LOG SQL Agent Job, ensuring that uncomitted transactions were rolled back.
Regards,
Ronan
Manually truncating the log weekly should not really be required. You can always check for the uncommited transactions using DBCC OPENTRAN command.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply