I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time. I have tried running the following:
WHILE 1 = 1 BEGIN DELETE TOP ( 4000 ) FROM [OLD_TABLE] WITH (TABLOCKX) WHERE [Date] < '2014-01-01 00:00:00' IF @@ROWCOUNT = 0 BREAK END
this seems to be working, but VERY SLOWLY... like 17+ hours, and only ~140,000 rows deleted.
The kicker here is this table and the others that need to be reduced are very large, around 50-60 million rows, and ~16-17 GB of space. There is only 20 GB of space left on the physical disk. I have looked at putting the data that I want to keep in an intermediate table, truncating and repopulating, but I am afraid that we will run out of space on the disk while these operations are completing. This is a production database. I have inherited this mess and I am trying to clean this up and am trying to get this DB cleaned up so we can stay functional until we can get new hardware in.
Are there any methods that I (a relative newcomer to database management) have not come across that may allow me to do this in a faster, more efficient manner?
*The table and column names have been anonymized.