Transaction log growth hufe when running REINDEX.

Question

Post reply

Transaction log growth hufe when running REINDEX.

m--S3qU3L

Hall of Fame

Points: 3706
More actions
June 2, 2010 at 10:21 pm

#222309

H all,
Any idea what could be the reason for the huge growth of transaction log files for some databases when running DB REINDEX.
John

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Are you backing up the transaction logs?

Are you shrinking the transaction logs?

Review the article in my signature on managing transaction logs. This should give you an idea of why your files are growing and what you can do about it.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 2

Hi,

Thanks for the reply.

Yes I take FULL & log backups.

I believe this is due to the huge REBUILING happening during the index maintenance. I thought of different mechanisms to overcome this.

Edit the REBUILD code to get include tran log backup after each REBULD (each object).

Edit the code to change the recovery model to BULK LOGGED and change back after the REBUILD.

Lets share the thoughts.

Cheers

John

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Once the transaction log has grown out large enough to support the rebuild, then it shouldn't grow anymore. If you are shrinking it after that process, all you are doing is forcing it to grow again.

Yes, you can perform more frequent log backups during the process to reduce how large it gets. And, you could change to bulk_logged recovery.

I would recommend not rebuilding all indexes. Use a smart indexing procedure to only rebuild indexes that need to be rebuilt. Lookup SQL Fool - she has a good provedure available.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 4

Hi,

What about the recovery with BULK LOGGED transaction log backups. Does switching the recovery between BULK and FULL break the LSN chain?

Cheers

John

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

No, switching from FULL to BULK_LOGGED and back to FULL does not break the log chain. If you switch to SIMPLE, that will break the log chain.

Just be aware that doing this will not reduce the log backup size. The log backups will still be the same size as they are now.

Do you have enough disk space available to allow the log file to be large enough to handle the rebuilds? If so, is there a specific reason why you want to keep the size of the log file smaller?

FWIW - I have databases where the transaction log is 24GB, others where the log file is more than 50GB to handle the daily operations. Once I had identified the largest size needed, I shrunk the file and manually grew it back out in 8GB chunks. This reduces the number of VLF's in the log file and improves performance of the log.

See Kimberly's blog posts on this subject at: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx for more information.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 6

Space constraint is the reason. Because the the extend at which the log file grows for few databases is undesirable eventhough we have space.

I believe when it is set as BULK LOGGED, the amount of information logged to the log file while REBUILD is less.

Thanks

John