Sql-server – Shrink of a big databases ? SQL Server

migrationshrinksql serversql-server-2008

In this moment I have a very big database (almost 4 TB) and wanna shrink data files for a future migration

Unused space : almost 3000 GB

I have in mind shrink data files day per day with low amount of data.

Any suggest to do it and avoid or reduce fragmentation?

Best Answer

Create a new filegroup. Duplicate the tables you want to keep into the new filegroup. Copy the data you want to keep to these new tables using bulk insert instructions with optimizations for ordered data. Drop the old tables.

When all tables are processed drop the old filegroup, if possible. If not, move the objects and data back again then drop the new filegroup.

From your figures I calculate you'll need about 1TB of disk to do this. Less if you delay implementing non-clustered indexes until after the old objects are dropped. Compression could make a significant difference, depending on the specifics of the data. Since you moved the data in the desired destination sequence there should be little or no fragmentation.