Transaction Log Growing to ~50GB

Question

Transaction Log Growing to ~50GB

Another DB Newbie

Old Hand

Points: 335
More actions
June 3, 2008 at 1:53 pm

#122648

One of my databases has a data file size around 30 GB. Used for OLTP. The transaction log is usually 200 - 300 MB. We perform daily full backups of the db and transaction log with 3 transaction log backups also occurring during the day. The database is set to automatically grow the data file by 20% and the transaction log by 10%.
Once a month a scheduled maintenance plan job does a Reorganize Index on all user databases on the server. For this database, and only this database, the transaction log grows to be larger than the database. For example, after the last run it ended up at almost 50GB! There is very little activity on the database between the last backup and the time the job starts the re-org.
This is the only database that does anything like this. Should the Reorganize be run more often? What should I look for to try to identify the problem?
Running SQL 2005 Standard Edition on 64-bit Windows Enterprise server . Clustered, failover, active/standby.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Re-org operation is same as DBCC DBREINDEX in SQL 2000. It needs space of approximately 1.2-1.5 times of database size for successful execution. To reduce the transaction log file usage try performing the re-org opeation after switiching database recovery model to Bulk-logged.

Change recovery model back to original one once re=org completes its execution successfully.

Manu

Another DB Newbie Old Hand Points: 335 More actions · Answer 2

THanks for the response. The current job is a scheduled maintenance plan built via the wizard with just the 'Reorganize Index' task selected along with 'all user databases'. How would I add the modification to bulk logging before the reorg and then back to full logging when done? Sounds like I would have to redo the current task and make it a script that goes through each user db, sets it to bulk logging, does the reorg (via dbcc?), then sets it back to full logging, making note of any and all errors, and of course with all of the activity logging that the maintenance plan currently gives me (e.g., logging errors to Windows Application Event Log).

That sounds quite involved, but I really do need to get this resolved. What would you recommend?

Another DB Newbie Old Hand Points: 335 More actions · Answer 3

I also should have mentioned that the database is on-line during this and can be taken off line. How much risk does the bulk-log change pose?

(It will probably be about a year before the app this supports is modified so I can take the database off line more often for maintenance).

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

No real risk, but I think you might need to grab a full backup when you change it back.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

As far as I can recall - index reorgs are always fully logged, so switching your recovery model will have no impact on what is logged during a reorg. On the other hand - index REBUILD's can be bulk-logged.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

lee-653046 Valued Member Points: 57 More actions · Answer 6

We came across this about 3-4 years ago.

A weekly SQL server agent job was reindexing a production database with a 1GB mdb and now and then the log would shoot to 20GB!

Afterwards we would sometimes hang Enterprise manage - activity manager.

A colleague up north came across a convoluted method to allegedly fix it.

We used a sledgehammer instead.

Backup database.

backup log with truncate only -- resets pointer

backup database again

sql command to shrink log.

backup full database again.

User has not complained since and performance on the old clunker is ok.

As I said - a bit of a sledgehammer.

Hope that helps.

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 7

I agree with Matt.

In one of my bulk-logged DB I do see a very big transaction log backup everytime a reindex has been perfromed. At the time of reindexing my DB has absolutely no logging of data. So its just reindexing that's being backed up during Tlog Backup.

All other times its a usual smaller Tlog Backup.

Ankit

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 8

One thing that you could do is to only rebuild or reorganize indexes that are fragmented. This means that you reduce the amount of log records that is generated and the time that is needed considerable.

In SQL Server 2005 you could use sys.dm_db_index_physical_stats to check the index fragmentation.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

http://msdn.microsoft.com/en-us/library/ms189858.aspx

I have a stored procedure that could help you with this.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

You could also use the script in Books Online.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Ola Hallengren

http://ola.hallengren.com