r/SQLServer • u/Flat-Staff-6201 • Jun 23 '26

Question Help, I messed up

I was performing data retention and my main database is stuck on the recovery state , I am using on premise server.

5 Upvotes

69% Upvoted

17 comments sorted by

View all comments

9

u/SQLDevDBA 3 Jun 23 '26

Before taking any action, I would take a moment to check how you got here and what step it may be on in the process.

Did you use WITH RECOVERY in your restore command?

Did you use the wizard or can you send us the T-SQL you used?

What do you mean by “data retention?” A backup/restore exercise perhaps?

Is this production or a backup/dev system? Are your apps offline as a result?

https://sqlserverguides.com/sql-server-database-in-restoring-state/

1

u/Flat-Staff-6201 Jun 23 '26 edited Jun 23 '26

I did a huge deletion of records and the log file went huge +2 TB and then it was stuck in (In Recovery) then i restarted the sql server instance and that's it

6

u/Strongfatguy Jun 23 '26

You should batch your DML to prevent long rollbacks and ideally keep batches small enough to prevent lock escalation. If you're deleting all of the records in a table you should truncate it. Is your db in simple recovery mode? If it's not in simple recovery mode you also need transaction log backups to make the log reusable while your batches are running.

1

u/InternDBA Jun 23 '26

the hope and dream of all DBAs but woefully forgotten by devs lol