August 2, 2010 at 8:34 am
Hi, This is going to put me in the mad house.
I'm running SQL 2000. I need to run reindex on certain tables from time to time to help performance. They get really fragmented, i.e. - Logical Scan Fragmentation ..................: 98.76%. (yes, there could be some bad stored procs that need evaluated, but I'm fine with just defragging. No, I don't want to use the defrag statment as I have to build a script that handles all the multiple indexes on the tables, no time for that. 😉 )
So, SQL is a failure in my situation.
If I reindex, my log file growes to almost 2X my DB size. My mdf is ~16GB, and after dbreindex, only on 5 tables, my ldf grows to 30GB+ filling my Log partition!
Ok, so now I need to use SHRINK to get the log size down.
Do you know that Shrink SEVERLY re-framents the files, undoing what REINDEX did?????
How the heck is this supposed to be managed???? It's catch 22.
How do I overcome this?
My log backups (every hour) can be slightly over 1GB (during a backoffice processing 1 time per week) , but typically are 100MB or less.
(Yes, larger drives could allow my huge log file, but I'm not ready for that yet. I just can't see how the log could ever need to be over 2X the Db file!?!) :hehe:
Help...
MP
August 2, 2010 at 8:39 am
Have you tried switching into bulk-logged recovery for the duration of the index rebuilds? Won't reduce the size of the log backups, should keep the log from growing. Also just rebuild the indexes that need it, don't do all of them.
Shrinking the log doesn't fragment indexes. Shrinking the data file fragments indexes. All that shrinking the log file does is force it to grow again, possibly causing lots of VLFs in the log, resulting in slow backups and recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 9:12 am
You may want to consider re-indexing table by table. Between two re-indexes you can also give a wait time of a minute or so. Also you can fire a transaction log backup between 2 tables.
This approach should help you especially when reindexing those 5 tables.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply