CODEWITHSUNDEEP
sqlsql-serverclonetruncate
Allen H.
Allen H.

Reputation: 358

SQL Server - cleaning up huge table. Insert data we want to keep and truncate old table

Our app has a few very large tables in SQL Server. 500 million rows and 1 billion rows in two tables that we'd like to clean up to reclaim some disk space.

In our testing environment, I tried running chunked deletes in a loop but I don't think this is a feasible solution in prod.

So the other alternative is to select/insert the data we want to keep into a temp table, truncate/drop the old table, and then recreate

  1. indexes
  2. foreign key constraints
  3. table permissions
  4. rename the temp table back to the original table name

My question is, am I missing anything from my list? Are there any other objects / structures that we will lose which we need to re-create or restore? It would be a disastrous situation if something went wrong. So I am playing this extremely safe.

Resizing the db/adding more space is not a possible solution. Our SQL Server app is near end of life and is being decom'd, so we are just keeping the lights on until then.

Upvotes: 0

Views: 851

Answers (1)

Nazim
Nazim

Reputation: 639

While you are doing this operation will there be new records added to the original table? I mean is the app that writing to this table will be live? If it is the case, maybe it would be better to change the order of steps like:

  1. First to rename original table's name to the temp
  2. Create a new table with the original name so that new records can be added from the writing app.
  3. In parallel, you can move the data you want to keep, from temp to the new original table.

Upvotes: 1

Related Questions