SQL Server Performance Forum – Threads Archive

Database growth issues

I’m wondering why things are doing what they’re doing. I have a fairly large database that, when viewed in taskpad, always seems to show the same proportion for free space within the database to used space. The datafile is set to automatically grow by 10mb and is unrestricted. The database seems to be growing in the middle of our dbreindex processes which produce memory errors in our sql logs. This is being run on the weekend when no users are in the database. The database is first set to simple mode to bypass the transaction log and then reset to full afterwards followed by a full backup. What can I do to prevent the database from growing during this operation? Is there anyway to shrink the amount of free space in the database to recover disk space? I imagine that dbreindex needs to grow something in order to perform its job…right?

Hi Quentin, yes the dbreindex process only uses free space at the end of the database file. If there is none, then it will expand the database to make some. You could shrink the database, but the sideaffect is that it will undo most of the good work done by the dbreindex. It would perhaps be better to shrink the database first and then dbreindex it. This would still leave free space in the database, but would give you defragmented indexes/tables Cheers
Twan

True.
How often do you rebuild indexes?
This grow affect your disk space?
Luis Martin
Moderator
SQL-Server-Performance.com

Thanks for the info. I rebuild indexes once per week. Yes it does affect my disk space. Is the space available at the end of the database determined by the amount of free space you want in the datafiles during a reindex?

I can’t follow you.
Can you post how do you run reindex? Luis Martin
Moderator
SQL-Server-Performance.com

First of all ensure there are no issues on physical disk space issues, add more space if current one is low.
DBCC DBREINDEX is a resource intensive operation and is always fully logged, regardless of the database recovery model setting. There is no way you can skip off this process. Better to place SQL database data files on high spec of disks to ensure no space issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>