Removing unused space from datafiles

saratcm
saratcm used Ask the Experts™
on
Hi All,
Will it be  good to schedule the 'Remove unused space fromdatafiles' option in the optimization jobs tab ? in the Maintenance plan window.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
I dont think so, that may shrink the datafile which may cause some performance issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Database Administrator
Commented:
This depends mostly on the structure, indexing and volatility (Inserts, Updates, and Deletes) of your data.  
What aneeshattingal is referring to, is that if you shrink the files, just so that later they have to grow and split pages, in order to insert new records, then the answer is no.  
If however, your structure implies that you create new records at the end of the file, and delete from the middle, you may benefit from such regular maintenance.  
Know your clustered indexes, and the volatility of your data before deciding.  
-G
 

Author

Commented:
Thanks for your commands,
How the database will allocate the space for the new data coming in, if it has free space in the middle of the datafiles (some free space may be bcoz of some deletions happened)  and we didn't shrink any files.
Will that free space remains like that or will it be used by the database to allocate the new data?
G GodwinDatabase Administrator
Commented:
That's where it will depend on your indexing.  Specifically clustered indexes (which by default is your primary key).
e.g. if you have a payments table that is clustered on payment date, the new entries will be placed at the "end" of the table.  Then if you process payments, and delete records from the table, that would presumably come from somewhere else in the table, and this could lead to empty spaces on pages.
On the other hand if the same table were clustered on account number, the inserts and deletes could be coming from pages distributed throughout the table.  Then, if  you were to shrink empty space from the DB, the database engine might later have to go insert a record where space had been reclaimed.  When this happens, it will split the page (making new empty space), insert the record, and then do all of the index maintenance required.  
Will that free space remains like that or will it be used by the database to allocate the new data?
If you delete large swaths of data, like moving data to a history table, you may completely empty data pages (depending on how your data is clustered). In this case, I believe the space would be reallocated within the DB.
Otherwise, in normal transaction processing, you would possibly have data remaining on partially empty pages.  This would prevent the space from being reused (except by inserts where the clustering of the table needs to put a record on that partially empty page.
-G
 

Author

Commented:
Good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial