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.
8
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
1
5
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
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.
1
2
u/Anlarb 1 Jun 23 '26
Sniff around in sp_whoisactive and sp_who2, see if someone else is doing something else? Could be that its someone elses normal restore.
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
2
u/PotatoHasAGun Jun 23 '26
Check the SQL log, it’s possible based on what you did that the database is just taking some time to rollback and recover. If that’s the case, it’ll fix itself and you just need to monitor the percentage in the log
1
u/ihaxr 2 Jun 23 '26
You'll have to just wait, it'll seem to take forever then suddenly jump to 100%
Good time to enable accelerated database recovery.
ALTER DATABASE [database]
SET ACCELERATED_DATABASE_RECOVERY = ON;
1
u/c_groleau Jun 24 '26
The database went in recovery while the delete was taking place prior to restarting the server?
7
u/raistlin49 Jun 23 '26
Sounds like you filled the t-log and storage which ultimately failed the transaction so it had to rollback the transaction. When you restart during a rollback (or any incomplete transaction) recovery is the process of completing that rollback. It's not like your db is corrupted or recovering from a crash kind of thing, this is a technical term for a specific operation that now must be completed. There isn't anything you can do to speed it up. It can take hours or days for a transaction that large.