What is Fastest way to shrink a datafile? I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server &gt; 2012, but that is not the subject of my question).
Before that, I'm going to move almost all objects from the main datafile of 4To to multiple datafiles on the same filegroup so I can get back the free space on the 4Tb file.
After moving objects, free space take like forever to be reclaimed with a dbcc shrink file with blocs of 256Mo.
What's the fastest way to get that space back to the OS?
Is there a way to use a backup restore to shrink the file?
DBCC SHRINKDATABASE
The fastest way I find to shrink a data file is to first use the SHRINK DATABASE command to see what it takes off the top. It will usually do this quickly--but not always. Keep an eye on blocking locks while you're doing this.
caveat
SHRINKDATABASE is impartial and will attempt indiscriminately attempt to shrink any file in the database. If you don't appreciate this behavior then don't run this one.
DBCC SHRINKFILE
After you've used SHRINKDATABASE you'll need to use SHRINKFILE. Here I find it's important to &quot;chunk&quot; the shrinks in small sizes. Also, I use a T-SQL DELAY to allow the database to gain back some resources to do other activities as needed. Using the script below you'll need to enter 1.&gt; your database name, 2.&gt; your logical file name of the file, 3.&gt; and the upper and lower bounds, in MB, for your upper database size in MB and 4.&gt; your lower database size in MB. You'll notice that it shrinks in 100 MB increments but, again, you'll need to find the size that's right for you. Warning: larger increments aren't necessarily more speedy. Don't worry &quot;too much&quot; if your lower bounds is too low--it won't/can't shrink past it. (You'll have to forgive my code, it's over 10 years old but still get's the job done).
--Cut and paste the code that this T-SQL generates and run THAT code against your DB
 declare @next int, @last int, @DELAY VARCHAR(255), @SHRINK VARCHAR(1000)
    
    SELECT @DELAY='WAITFOR DELAY ''000:00:01'''
    select @next=102000, @last=50000
    while(@next &gt;=@last)
    begin
    --print @next
    SELECT @SHRINK ='use [&lt;your DB name&gt;] DBCC SHRINKFILE (N''&lt;enter logical file name&gt;'','+convert(varchar(255),@next)+ ')'
    print @shrink
    print @DELAY
    print 'go'
    select @next=@next-100
    end