August 4, 2009 at 2:57 am
I have a 40Gb database in SQL Server 2005. After reindexing all the indexes in the database found that the database size increased to 75Gb.
Any particular reason for this huge size increase ??
Thanx in Advance !
August 4, 2009 at 3:12 am
Can you provide a bit more detail...what has grown to 75GB? What size is the database itself? How much space is free in the database? What size is the transaction log etc etc?
If your database is in FULL recovery mode, then re-indexing can use up a large amount of space in the transaction log. Therefore, it's possible that it's the transaction log that has grown, not the database.
August 4, 2009 at 3:29 am
The whole database size is 75Gb... The Database recovery model is set to SIMPLE and as expected the log file size is very less... It's around 1Gb... The data file is the one that has grown... It's around 74Gb...
Well i'm not sure about the free space in the database... Correct me if I am wrong... When checking the properties window for the database in the General tab I can find Space Available... The Space available is around 39Gb...
If this is the freespace then how do I reclaim it ??? Or Is there a need to reclaim it from the performance point of view ??
August 4, 2009 at 4:18 am
santhosh (8/4/2009)
If this is the freespace then how do I reclaim it ??? Or Is there a need to reclaim it from the performance point of view ??
Yes, that is unused space, and you can reclaim it by running DBCC SHRINKDB or DBCC SHRINKFILE (See BOL for full details), but...
You need to find out why it grew in the first place (it may not be the re-index). Whatever it was may just cause it to grow again after you have shrunk it.
You don't want it to autogrow while users are on the system, as this will block them for the time it takes to expand. Growing and shrinking the files will also cause file fragmentation, damaging performance.
That seems like a huge jump in size... what are your autogrowth parameters set to?
August 4, 2009 at 4:33 am
Data file has an autogrowth set by 1 MB (Unrestricted) and the Log file has an unrestricted autogrowth of 10 Percent...
August 4, 2009 at 4:49 am
santhosh (8/4/2009)
Data file has an autogrowth set by 1 MB
Ouch! If the file has grown from 40gb to 75gb by 1mb a time, then you could have 35,000 file fragments!
First step, find out what is causing the growth.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply