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:
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.
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