0

I have a backup table that - thanks to poorly planned management by a programmer who is bad at math - has 3.5 billion records in it. The data drive is nearly full, performance is suffering. I need to clean out this table but just a simple SELECT COUNT(1) statement takes 30 minutes or more to return.

There is no primary key on the table.

The database uses SIMPLE logging. There's only 25gb left on the drive, so I need to be mindful that whatever I do has to leave space for the database to continue functioning for everyone else. I'm waiting for confirmation as I type this, but I don't think I need to keep any of the data that's in there now.

On a table with that many records, would TRUNCATE TABLE grind the system to a halt?

Also looking into the solutions proposed here: How to delete large data of table in SQL without log?

The idea is to allow my clients to keep working while I'm doing all this.

Bill in Kansas City
  • 360
  • 1
  • 5
  • 21

1 Answers1

0

Truncate table would work if you no longer need the records. This will not reduce the size of the database. If you need to do that you would need to shrink the data file.

If you would rather delete the records Aaron Bertrand has good examples and test results he did located here: https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

Joe
  • 147
  • 1
  • 8