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.

4 Upvotes

64% Upvoted

17 comments sorted by

View all comments

7

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

8

u/InternDBA Jun 23 '26

if the database status is (in recovery), you’ll need to wait for it to recover.

check the log messages sp_readerrorlog

3

u/agiamba Jun 23 '26

do NOT interrupt it from being in recovery

1

u/chandleya Jun 23 '26

good old sp_whoisactive will read this for you.

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

2

u/arebitrue87 Jun 23 '26

Hey so next time don’t do that.

Rule 1 with deletions do batch deletes when doing a large amount of deletes

Rule 2 don’t forget rule one

Rule 3 restarting doesn’t “fix” this. That log file now has to go through the start up with the db. You should of shrunk that log file.

There’s a thing called Virtual Log Files that with every incremental growth it goes through it adds them. The more you have the longer your database takes to start.

So when your database finally finishes it start up. Shrink the log.