title: Shrinking SQL Server database that had most of the data deleted
body: <p>I am working on a database that has been restored from a backup. It is then getting a large portion of the data deleted from it so that we can have a smaller subset of the data for dev and testing efforts. For example, we are deleting old orders.  </p>
  
  <p>All of this results in a database that was about 2 TB only having 100 GB of data in it. So I need to shrink it. The problem is that the script I was given by the previous dev is taking longer to run than the entire rest of the restore, and data reduction process.</p>
  
  <p>The previous dev gave me the below script that loops over and reduces by a portion of the needed amount each time using <code>DBCC SHRINKFILE</code>. This is what we are using right now but I am sure this is not the best solution as it takes a really long time and I'm sure the amount it is fragmenting things is horrifying.</p>
  
  <p>If it is any help we are using both Redgate and SQL Server to create backups that the developers can use to create their working databases.</p>
  
  <pre><code>DECLARE @FileSpace int;
  DECLARE @MinSize int
  DECLARE @Size int;
  DECLARE @FileName nvarchar(100);
  
  SET @Size = @FileSpace;
  
  WHILE @Size &gt; @MinSize
  BEGIN
      SET @Size = @Size - 1000;
  
      DBCC SHRINKFILE (@FileName , @Size)
  
      SET @Size = (select database_files.size/128 from sys.database_files where database_files.name = @FileName);
  END
  </code></pre>
  
  <p>I'm really needing something that will be much faster because at present it has taken well over 3 days.</p>