June 8, 2009 at 7:17 pm
Hi All, We have a 60 gb database. The index is rebuilt every night. But for some reason the tran log filled up last night during the index rebuild. The log is sized at 25 GB. We never had a single occurance when the log was filled during the index maintenance. There was no other scheduled jobs or user jobs running at that time. This caused external user requests to time out. Can someone point to potential cause for this issue?
Thanks
Jay
June 8, 2009 at 9:43 pm
When an index is rebuilt the new index is written to the data file, and the space for the old index is de-allocated. But the process of writing the new index fills up space in the transaction log equal to the size of the index.
To know more why Tlog filled up during this process :
>> Was rebuild index job running in off peack hour?
>> There might be some updates or inserts operation so whikle rebuilding it has generated so much log.
What is recovery model you had whicle rebuilding index check if it was running earlier with simple or bulked logged if yes then while rebuilding index it will minimally log the Tlog.
>> Do you have regular schedule to backup DB and Tlog?
>> Check was there any open transaction?
"More Green More Oxygen !! Plant a tree today"
June 9, 2009 at 1:58 am
Hi,Thanks for the reply. The insert/update operations at that time was no different than any other day. The recovery model was not changed ,its always in full recovery model. The Tlogbackups run every 5 minutes and usually the index rebuild completes in 5 minutes.
Thanks
June 9, 2009 at 5:16 am
jayanth (6/9/2009)
Hi,Thanks for the reply. The insert/update operations at that time was no different than any other day. The recovery model was not changed ,its always in full recovery model. The Tlogbackups run every 5 minutes and usually the index rebuild completes in 5 minutes.Thanks
try DBCC SQLPERF and see how much of log file has been used.
What is your Autogrowth setting? Was the rebuilding index job successful? Are there any open transactions that makes the Tlog filled up?
Did you think of changing the reocvery model to bulk logged while re building the indexes?
June 9, 2009 at 6:05 am
The autogrow is turned off for this database. The index maintenance ran sucessfully today. But still not sure why it didn't complete yesterday.It filled the log yesterday and never finished.
Thanks
June 9, 2009 at 6:31 am
is it absolutely necessary to rebuild indexes nightly, normally once a week should suffice
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2009 at 7:46 am
The index is rebuilt or reorganized every day based on fragmentation level. Never had an issue till it failed yesterday.
June 9, 2009 at 8:09 am
even so it probably shouldnt be necessary on a daily basis.
although its never failed previously remember the database will change. Extra indexes may have been created, more data now than you had previously??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2009 at 10:06 am
jayanth (6/9/2009)
The autogrow is turned off for this database. The index maintenance ran sucessfully today. But still not sure why it didn't complete yesterday.It filled the log yesterday and never finished.Thanks
Yes the job failed because it filled up the log and there is no space for further transactions to be recorded in the Tlog. However, does your error log or event log say anything suspicious? And May I ask you how many indexes are your rebuilding?
June 9, 2009 at 11:49 am
But why the log filled yesterday when its been running fine for ever is a mystery to me. There are about 15 indexes that are either rebuit or reorganized.
June 9, 2009 at 11:58 am
you didn't mention open transactions. Those can prevent the log backups from freeing space.
Also, did the log backups change in size? I used to track this rather than the actual log size since it clued me in that things changed in a specific point in time.
June 9, 2009 at 4:07 pm
jayanth
To cut to the chase.
When you rebuild indexes in full recovery mode (as I presume you are), the activity is fully logged.
Whether this fills your transaction log depends on how often it is backed up.
So you have one process doing an activity which adds to the log, and another that makes it available again.
There are many potentially more complex explanations for this, but the most likely is that your rebuild fills the log quicker than the transaction log backup removes it.
Bear in mind that a basic full rebuild (with no transaction log backups) can consume virtually as much space as the full database under some circumstances.
Tim
.
June 9, 2009 at 5:00 pm
Based upon what I have read here - my guess is that you rebuilt an index (or attempted to rebuild) that has not been previously selected. It now was selected and needed a lot of space in the transaction log to be rebuilt.
But then again - this is just a guess...;-)
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
June 10, 2009 at 5:54 am
Perry Whittle (6/9/2009)
Extra indexes may have been created, more data now than you had previously??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2009 at 9:59 am
I found the log backups increased in size significantly(from few megs to over 20 GB ) around the time the index rebuild failed. The index rebuild happen only if the fragmentation is over 10%, so i think the fragmentation never hit the 10% so the index might have never been rebuilt, so it didn't need a bigger log file. Thanks for proving the clue.
thanks.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply