June 16, 2008 at 10:20 am
Using SQL 2000 Std running under W2k Server (both latest SP).
We use a modified version of the BOL script to re-index our databases on varying schedules based upon usage. The script is passed a single parameter which is used to DBCC DBREINDEX based upon the fragmentation.
We have one database that was overlooked for this and as a result has not been reindexed in over a year. The recovery model is 'Full' since we use AuditDB for auditing transactions. Growth is slow and performance is acceptable, the oversight was discovered during an internal audit.
When I tested the reindex script on this DB on a dev server, everything worked without issue - except, the properties tab in Enterprise Manager for this dB showed Space Available equal to over 50% of the database size (prior to reindex it was 10%).
I understand that DBCC DBREINDEX also updates the stats, but I ran sp_updatestats afterwards and the values remained the same. I was sure the available space was incorrect, but could not find a way to get Enterprise Manager to update the info correctly, so I ran DBCC SHRINKDATABASE (no, we never shrink production databases - just wanted to force the stats to update).
Prior to Reindex, the dB was approx 1GB in size, with 10% free.
After (initially) it was about 5% larger, but availalbe space was over 50% (over 500MB).
Forcing the shrink, the resulting size was 700MB or 30% smaller than the pre-reindex size.
My questions are:
1) Why would the 'available space' on the properties tab be incorrect post REINDEX and how can I get it to update w/out forcing a SHRINKDATABASE?
2) Why would a REINDEX cause the database to decrease 30% in size? I'm assuming that would indicate a huge amount of fragmentation in the indexes, which did not seem to be indicated in SHOWCONTIG, but I do not know of any other reason.
3) Due to the extent of the rebuilding, with recovery mode set to FULL, the transaction log was huge after testing the REINDEX (1.5x the database size). For the purpose of testing, I set recovery mode to simple, ran the script to save time/space then set it back to FULL. When implementing this on production, is there a downside to changing the recovery mode temporarily (for the reindex) - assuming it is done in the offhours? Obviously the database would not be in use and the log would be backed up just prior to performing this function.
June 16, 2008 at 10:34 am
The space statistics sometimes don't update correctly or on the time you want. I haven't ever worried as this is a trend, longer term number and not a spot number.
The space could decrease as you might have pages or extents that are hardly used because of fragmentation, but it's possible that you substantially clean things up. I'd look at the fillfactors as well. If they are high (little free spacE), you might have really changed this.
That being said, it does seem like an extreme number. I wonder if your first number might have been off a little as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply