Transaction Log Growth Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Transaction Log Growth Issue

Author

Topic

mclifford
Starting Member

2 Posts

Posted - 2008-04-08 : 16:50:54

I have an issue with the Transaction Log growing on it's own for a particular DB and not truncating as it should. I'm hoping that someone has some insight that can help me figure out what may be causing this.

This DB has regular scheduled backups happening daily, which includes Transaction Log backups. The transaction log doesn't truncate during backups though. However, if I run a truncate log on chkpt statement, with DBCC ShrinkDB separately then the log file truncates. I've tried restoring this DB on an isolated server where it is not having any transactions running to it at all and the transaction log file still grows on it's own without truncating during regular daily backups.

I thought maybe the log file itself was corrupt, so I tried detaching the DB and attaching just the Data file so that a new Log file was created, but the issue continued.

This one really has me stumped. Hopefully someone can point me in the right direction.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 16:59:58

"so I tried detaching the DB and attaching just the Data file so that a new Log file was created, but the issue continued."
union all

This is really bad practice.

"However, if I run a truncate log on chkpt statement, with DBCC ShrinkDB separately then the log file truncates."

This step is usually done when by no means you can shrink Transaction log file. If you do this, you will break Trnsaction log backup LSN.

(Day-to-Day recovery)
1)Try doing log backup every 15-30 minutes
2) Increase disk space for Log files .

Immediate solution:
1)Backup database.Put database in Simple recovery mode (which is like truncate log on chkpt statement) and shrink log file if you can't shrink log by any means

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 17:23:09

See
http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-04-09 : 13:15:55

the following code is to truncate and shrink the database.....

BACKUP LOG databasename WITH TRUNCATE_ONLY
print 'started shrink database'
print getdate()
DBCC SHRINKDATABASE (databasename)
print 'ended shrink database'
print getdate()
print 'started update usage'
print getdate()
DBCC UPDATEUSAGE(databasename)
print 'ended update usage'
print getdate()

mclifford
Starting Member

2 Posts

Posted - 2008-04-09 : 15:36:35

Thanks everyone for the input so far.

Sodeep; I've actually put a copy of the DB on a test server to perform all the troubleshooting I've done so far, so all those nasty little things I've done have not been on a live DB. Thanks for the warnings though, the concern is appreciated.

I guess I should be a little more thorough in my descriptions of what is actually happening here.

With this type of DB I've got both Data and Log file growth set to automatic at 11MB in order to restrict growth somewhat. In testing with other similar DBs I have put together, the Log files are successfully truncated when a Full DB Backup is performed through Enterprise Manager, however, this particular DB does not seem to want to do this and the Log files will continue to grow unabated (11mB at a time mind you).

For the time being I've set up a daily scheduled task that backs up the DB through the following statements;

BACKUP LOG (database name) TO (backup filename) WITH NOINIT
BACKUP DATABASE (database name) TO (backup filename) WITH INIT

I then have the backup file zipped up and put in a safe backup folder. I then have the following statements run;

EXEC sp_dboption 'database name', 'trunc. log on chkpt.', 'TRUE'

use (database name)
checkpoint
go

DBCC SHRINKDATABASE (database name, 10)

use master
go

EXEC sp_dboption 'database name', 'trunc. log on chkpt.', 'FALSE'

This series of statements will successfully shrink the log file size down each night, but it seems like just a bandaid for the actual issue of the log file not truncating during a full database backup.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-09 : 16:39:05

"In testing with other similar DBs I have put together, the Log files are successfully truncated when a Full DB Backup is performed through Enterprise Manager, however, this particular DB does not seem to want to do this and the Log files will continue to grow unabated (11mB at a time mind you)."

Full backup doesn't truncate the log. You need to have Log backup in place (every 15-30 mins) to keep log file in control.

For immediate need:
1)Backup database.Put database in Simple recovery mode (which is like truncate log on chkpt statement) and shrink log file(not whole database) if you can't shrink log by any means
Read article given by nr carefully.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources