Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

shrink data files Expand / Collapse
Posted Thursday, July 25, 2013 9:21 AM


Group: General Forum Members
Last Login: Thursday, October 20, 2016 2:00 PM
Points: 498, Visits: 2,839
we have deleted some rows and released around 500 GB of space from a table. We applied filters so that this table doesn't grow beyond 600 GB. The table grew to 1.2 TB size. Now we are planning to release the space to OS as that drive holds some other database files also. But the shrink file process is taking too long. Almost 10 hours to shrink 11 GB. That database have LOBs. I am planning to shrink just one data file which has almost 400 GB of free space. I planned to add new file to the existing file group and emptying the file and dropping the old one. But the file group already contains two files and if I add a new file and do the empty file operation, to which file the data will go. Is it the new one or old one? One of the file is set to restricted growth. I am not sure how this process works? Any suggestions on how to release the free space?
Post #1477567
Posted Thursday, July 25, 2013 12:53 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 5, 2016 10:57 AM
Points: 645, Visits: 1,930
How busy that table or database is? If you have users continuously using that table and running queries all the time, it will affect the shrink process and slow it down. If you have a weekly or monthly downtime period and your request is not an urgent one, you may be better shrinking the file that time or maybe during off peaks hours.

Now, regarding to which file the data will go (assuming you have three files and you're emptying one) I think that the data will be evenly dispersed to each of them because both of them are available and they have free space. But I am not so sure about that.
Post #1477682
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse