SQL Server Space Reclaim

Question

We have a sizeable SQL Server 2016 database (running on Windows 2016 Datacenter). A table was growing exponentially due to a bad SQL process. The bad SQL process has been resolved, but the specific table is still large, and the database instance grabbed a lot of space on the server. That is, the database instance's physical size is very large.

We need to reduce the physical size and "shrink database" is not recommended by many because of the fragmentation it causes. To process this while keeping everything on the same server, is the best thing to:

1. Deal with specific table

   a) Create a script that will build a new table and copy records from original table. But not copy excessive records created by bad SQL process.
   b) Then delete the original table and rename the new table.

2. Deal with general database

   c) Create a script to clone the database schema and use this to create a new database.
   d) Then script the data in chunks from the current database to the new database (because data is extensive).
   e) Drop the current database and rename the new one.

   However, if space is an issue on the server to achieve this, could I:

   f) Script the entire database WITH SCHEMA and DATA
   g) Drop the current database
   h) Use script to rebuild the entire database

Answer 1

Delete the extra data out of regular business hours, then shrink the database. Don't overcomplicate things.

Andy Mallon explains how in the post How to shrink a database in 4 easy steps:

1. First you'll get the file sizes for the databases on your instance, using the sys.database_files system view.

2. Then using DBCC SHRINKFILE, you'll shrink each file in question.

3. Finally, you'll rebuild all your indexes to ensure any fragmentation is handled.

Answer 2

Do you have the space on the server for your #1 option?

If so, then I would do that, making sure all recent activity is in your new table before dropping and renaming. I would definitely try to do it during an outage so that you're not at risk of missing records or errors during the transition, if possible. Also, if the database is in the FULL recovery model, you might consider looping your table inserts with log backups if it will be a large amount of data you are keeping.

Answer 3

Here is an alternative I suggest.

1. DELETE the unwanted records in a chunk (5k, 10k, 20k) not to cause significant blocking.

2. Then, take a maintenance window to shrink the database in a chunk to an acceptable size by slowly increasing the chunk size.