April 29, 2009 at 11:32 am
I have one database in my system,less frequently used with low transaction rate on them
We have sql server event alert for error number-9002 which indicates full log.
every sunday when my rebuild index job runs in midnight that time i will get plenty of
alerts for full log on database.this is sql 2005.EE on 64 bit
we have trn log backup job as part of maint plan that runs every 30 minutes daily.
since not many important user trnsactions,i changed recovery for my db to bulk-logged from full.
when i did dbcc sqlperf i can see it has plenty of free LOG space.
But only during rebuild index task,space gets full and i m getting lots of emails.
what r the next steps that i should perform now..?Thanks
April 29, 2009 at 12:06 pm
dallas13 (4/29/2009)
I have one database in my system,less frequently used with low transaction rate on themWe have sql server event alert for error number-9002 which indicates full log.
every sunday when my rebuild index job runs in midnight that time i will get plenty of
alerts for full log on database.this is sql 2005.EE on 64 bit
we have trn log backup job as part of maint plan that runs every 30 minutes daily.
since not many important user trnsactions,i changed recovery for my db to bulk-logged from full.
when i did dbcc sqlperf i can see it has plenty of free LOG space.
But only during rebuild index task,space gets full and i m getting lots of emails.
what r the next steps that i should perform now..?Thanks
Rebuild only the indexes that are actually fragmented. Not ALL.
* Noel
April 29, 2009 at 12:14 pm
There are a few scripts on the web that will rebuild indexes if they exceed a certain level of fragmentation only. Otherwise, they will defrag them.
I've seen some cases where the script puts the database in simple recovery mode, reindexes it, switches back to full recovery mode, then takes a backup.
April 29, 2009 at 12:24 pm
Thats a good option to use scripts.
But is there any possible solution for this situation..?
Like if I start taking my trn log backup job for every 15 minutes for that particular time of rebuild task..
will that be helpful..?
So what is exactly happening during rebuild task that makes my db log file full even though it has not many transactions
April 29, 2009 at 12:34 pm
If the rebuild of a table starts at the last VLF you are likely to run out of space if the space is tight.
You should, BEFORE running the re-index, FORCE a Transaction log Backup.
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply