August 23, 2004 at 12:02 pm
sql7 on w2k:
I have a 55GB database. I run the Optimization step of a maintenance plan with these parameters: Reindex with 10% freespace and shrink database to 10% freespace.
Why then after this job runs my datbase grows to 105GB. The freespace in each filegroup is approx 50% of the total space.
Thanks for any help.
Randy
August 23, 2004 at 1:04 pm
On your maintainence plan what is the point at which a shrink will occurr in MBs. The index rebuild will leave a lot of free space many times, I am thinking that setting may be wrong.
August 23, 2004 at 1:13 pm
Shrink database when it grows beyond: 50MB
Amount of freespace to remain after shrink: 10% of the dataspace.
August 23, 2004 at 1:13 pm
You may be confusing index free space with database file free space.
Index free space refers to the free space in each page of an index. When an insert operations occurs, a page may need to be split to insert the row. When a page split operation occurs, this hinders performance.
By default, SQL Server creates an index with 70% fill factor, or 30% free space. By setting this to 10%, you are actually overriding what Microsoft (and me, for the matter) think is a better setting for this parameter.
In my production environment, the last step that is completed in the maintenance plan is the shrink. Typically this leaves the transaction log 20%-30% larger than normal, with very little free space.
After the maintenance plan is complete, the transaction log is backed up. After this, everything gets back to "normal".
So, what you are seeing is normal to me.
Try backing up the log after your maintenance plan has completed.
Good Luck
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 23, 2004 at 1:20 pm
mljohn,
It is not the log file that is in question. The .mdf and the .ndf both contain 50% freespace.
If the index freespace is 10% and the data freespace is 10%, then I would expect to see the freespace in the .mdf and .ndf approximate 10%.
Instead there is 50GB sitting empty.
Regards.
August 23, 2004 at 1:48 pm
Sorry, slight misunderstanding.
But, I still stick with my original thought that this is normal, or at least not unusual, after doing a reindex and a shrink.
Are your database file sizes fixed or set to autogrow? All of my production databases are set to autogrow, typical free space is in the 30-35% range.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 23, 2004 at 1:57 pm
They are set to autogrow, unrestricted.
August 23, 2004 at 2:10 pm
Randy,
To get at your basic question (why does the database grow so big)....it's the transaction log that is growing. When you Reindex or Defrag the index, SQL Server needs to keep track of what's happening in case it has to rollback the changes to the index. So the transaction log grows to store that information. Once the reindex/defrag is done, then that excess log space is marked as inactive.
Inactive space can not be shrunk until it's at the 'end' of the log files. This might take a while. So, you might request 10% free but it could take SQL Server a while to get it to that point. Refer to the BOL, use the Index tab and enter Transaction Logs, truncating.
So, bottom line....it's normal for the database to grow in size when reindexing or defragging the indexes - it's actually the log growing. It's also normal for the inactive portion (free space) to not be released immediately.
-SQLBill
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
August 24, 2004 at 7:29 am
Sorry SQLBill,
It really is the .mdf and .ndf files that contain 50% freespace. The .ldf grew somewhat and I expect it to shrink somewhat after the weekly backup.
But I am looking at the database in Enterprise Manager using the 'Space Allocated' tab and the .mdf and .ndf files indeed are only half full.
This has happened other times this Optimization step of the Maintenance plan ran and my only solution was to turn on autoshrink on Friday evening and turn it off Monday morning.
But I was hoping to avoid this waste of resources by finding how to prevent this glitch from happening in the first place.
Regards,
Randy
August 24, 2004 at 7:55 am
Randy
Do you have any Binary, Image or Text fields in the DB ?
SQL7 has a bug that causes exactly this problem. I went back and forth with MS Support, and was actually able to build a script that would reliably do just that. It "Shrank" a 300 meg DB to about a gig.
They did not have a resolution, except to move to SQL2k, the problem would not be fixed in 7.
Can't remember what we did to resolve it initially, software issue's kept us on SQL7 for a while. We've moved it to 2k and the problem disappeared.
KlK, MCSE
KlK
August 24, 2004 at 8:54 am
We found that putting the text and image stuff in it's own data file would correct this issue. I seem to remember and MS article on it somewhere but I ran into that previously myself where the index would grow the file but the text datatype would not allow the DB to shrink back. I don't recall there being any issues with the data file where the text data was stored afterwards as we dropped warehousing the related data over 2 years ago.
August 24, 2004 at 10:04 am
There are not any binary, image, or text columns in this particular database.
August 24, 2004 at 1:36 pm
What is the size of your lagrest table?
August 24, 2004 at 2:03 pm
The largest table is 83,000,000+ rows and 32GB.
August 24, 2004 at 2:16 pm
I guess there must be clustered index defined in this table. If it is, your database needs 1.2 to 1.5 times free spaces of the largest table depending on how many indexes the table has to rebuild the entire indexes for this table. That is about 38 to 48GB. That is why you see database was growed to about 105GB after the reindexing.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply