SQL Server 2012 – Resolving Recovery Pending Error

recoverysql-server-2012

We are running a series of Delete Queries to free up space on our SQL Server (2012) I was able to recover files after the "Recovery Error", but it is very time consuming.

So I keep the size of my deletes to about 1 million records, and this seemed to prevent triggering the "Recovery Pending".

Is there a better approach?

Thanks much

Best Answer

Use a loop to delete and make frequent transaction log backups while doing so. That way people can use the table and you will keep the transaction log at a responsible size

DECLARE @ROWCOUNT int = 1
  WHILE @ROWCOUNT > 0
BEGIN
 delete  top (10000)
  from [SomeTable]
  where [Something]
 set @rowcount = @@rowcount;
END;
Related Question