April 27, 2009 at 9:02 am
We have a maintenance plan that for 2 weeks has been crashing on the step that reorganizes the indexes about 2 and a half minutes into the step. We run that twice a week at 3am on mondays. Prior to these errors we switch the database from simple to full backup mode. Here are the errors I am getting the the associated t-sql from the view t-sql button
Executing the query "ALTER INDEX [ItemIdentification_PK] ON [dbo].[ItemIdentification] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The transaction log for database 'DAReporting' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
ALTER INDEX [ItemIdentification_PK] ON [dbo].[ItemIdentification] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
Now the DB size is 6 gig and is moderately used. We have the initial size of the Transaction log to be 20 meg, increased by 10% to a max of 2 gig .. any other time of the day when we monitor this file it doesn't even approach 100 meg .. much less 2 gig. Is it possible that since switching to full backup mode it could use that much space on the Transactio Log that I should increase this allowance further .. or could it be something like the TEMPDB running low on space, I was assuming it couldn't be tempdb since the SORT_IN_TEMPDB parameter is not set by the maint plans but being a relative sql newb .. I figured I would asks those wiser then me in such ways. (I have been searching through books online and other blogs but couldn't find anything that directly answered my issue.) Thanks in advance to all responses!
April 27, 2009 at 9:09 am
when you switched to full-backup mode, did you schedule t-log backups? as it looks like you have run out of room in the t-logs
April 27, 2009 at 9:17 am
Yes .. last week after it bombed the first time I switched it from doing backups of the tlog from every 4 hours to every 2 hours ...
April 27, 2009 at 10:05 am
jspatz (4/27/2009)
" failed with the following error: "The transaction log for database 'DAReporting' is full.
Pretty much says it all. Either backup the log more frequently, especially during the reorg, or increase the size of the log to accomodate the process.
-- You can't be late until you show up.
April 27, 2009 at 10:06 am
Why would you switch from simple recovery model to full recovery model to rebuild indexes? You should already be in full recovery model and performing transaction log backups on a regular basis.
The problem you are having is because you are in full recovery model and the transaction log is filling up to your maximum of 2GB. Modify that setting and change it to unrestricted - set it to 2000MB and have it grow in 500MB increments.
Run your rebuild operation and see how large the transaction log gets. Once you know how large it is - leave it there and don't shrink it. You will need that space again the next time you rebuild your indexes. It does not hurt to leave it at that size.
Please review the article in my signature on Managing Transaction Logs for further information.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 10:10 am
Thank you all for your replies, I will up the transaction log size .. I just wanted to make sure that was the correct solution and not something with the TEMPDB .. reading numerous articles makes you start thinking there is more to it then there is 🙂 .. and just an fyi .. when I was handed the DB, it was already in Simple and I wanted to move it to full, and the reorg indexes was already part of the existing maint plan ..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply