I’ve recently been asked by some folks about different states for SQL Server databases. Questions like “what’s the state of a database with log shipping?”, “can I fix a database in (whatever) state with a restart?”, and “which is the bad one, RECOVERY or RECOVERY PENDING?”
Hopefully you don’t have to try to figure out if your database is in a bad way or not because of an unusual state, but I presume when you do you will need to know as soon as possible. So, I’ve put this handy list of possible database states together to help you in your moment of need.
And before we begin, I know Microsoft has a page about database states, but let’s be honest, it’s a bit sparse with the information. Hopefully this post can add to that, and answer some of your common questions about different database states, and do so in a hurry.
ONLINE
What does this mean? All files are online and the database is available for access. Your database is up and running. People can connect and execute queries of varying quality. This is the state you want every database to be in pretty much always.
Is this bad? No, this is the desired state for a functioning database.
Can I change this with a restart? No. Well, not unless you are an anarchist and want to live dangerously by moving data files around or changing the permissions for the SQL Server service.
Any other notes? This is the best state for a database with the primary filegroup online, but if you just restarted the instance then the undo phase of recovery may not be complete yet. The database is available, but a few rows might still be locked while uncommitted transactions get rolled back. If users complain about weird blocking right after a failover or restart, this could be why.
Also, you could still have corruption, deadlocks, or runaway transaction log growth in this state, so be ready for anything.
OFFLINE
What does this mean? Someone explicitly set the database to this unavailable state.
Is this bad? It depends entirely on whether you meant to do it. If you took it offline to move a file to a different drive, then no, that’s normal maintenance. If you show up Monday morning and a production database is OFFLINE and nobody knows why, yes, that might be bad. Check the error log and ask around.
Can I change this with a restart? No. Restarting SQL Server won’t bring an OFFLINE database back. But you should be able to bring it to ONLINE with ALTER DATABASE [YourDB] SET ONLINE to get it back.
Any other notes? 99% of the time OFFLINE is a deliberate choice, not an accident. We recommend you find out who and why before you bring it back ONLINE, especially if it wasn’t you.
RESTORING
What does this mean? A restore is in progress for one or more files of the primary filegroup, or a restore was started with NORECOVERY and is waiting for more log backups before being recovered. Either way, nobody can use it right now.
Is this bad? Not by itself. It’s normal during a restore. It’s only bad if it’s stuck there when you didn’t expect it to be, or if a restore failed partway through and now you can’t get it out of the state.
Can I change this with a restart? No, and please DO NOT try. Restarting SQL Server while a database is RESTORING does nothing helpful and can leave you with a bigger mess. To finish the restore and bring the database online, run RESTORE DATABASE [YourDB] WITH RECOVERY. That tells SQL Server “I’m done restoring, finalize it.”
Any other notes? You cannot set a RESTORING database to OFFLINE, ONLINE, or EMERGENCY. The only way out is to finish the restore (…WITH RECOVERY) or drop the database and start over. If your restore failed because the backup file is corrupt, you may have to drop and restart from a different backup.
Also, this is the expected state for your databases that are being copied via Log Shipping.
RECOVERING
What does this mean? SQL Server is currently running recovery on the database, replaying the transaction log to bring everything to a consistent state. You’ll typically see this right after a restart, a failover, or bringing a database online.
Is this bad? No, this is normal. It’s a transient state, so sit tight and let it finish. If recovery succeeds, the database moves to ONLINE on its own. If it fails, you end up in SUSPECT, which is a different conversation.
Can I change this with a restart? No, and restarting in the middle of recovery just makes recovery start over from the beginning when SQL Server comes back up. You’re not helping, you’re prolonging the wait.
Any other notes? If recovery is taking an exceptionally long time on a big database, that may be expected. A database with a giant transaction log or a long-running uncommitted transaction at the time of shutdown can take a while.
Check the error log, since SQL Server reports recovery progress there as percentages. Also, read about and consider Accelerated Database Recovery if you are using SQL Server 2019 or later.
RECOVERY PENDING
What does this mean? SQL Server tried to start recovery but bailed out because of some resource problem. The database isn’t necessarily damaged, but something is in the way. Common reasons include a missing data file, a missing log file, a permissions issue on the file, or the drive isn’t mounted.
Is this bad? It’s not good, but it might not be serious. The data is probably fine, but you just need to fix whatever is hindering recovery.
Can I change this with a restart? Often, yes, if the underlying problem was due to a drive being offline when SQL Server tried to start, but now the drive is available. In those cases, restarting the SQL Server service might let recovery finish. But if a file is genuinely missing, a restart accomplishes nothing.
Any other notes? Look at the error log first. SQL Server tells you exactly why it couldn’t recover the database. Most of the time it’s a missing file or a permissions issue, and once you fix that, it’s easy to get back in business. Don’t panic and don’t reach for repair commands until you’ve actually read the error message.
SUSPECT
What does this mean? SQL Server tried recovery but it failed. The primary filegroup appears damaged, and SQL Server can’t bring the database to a consistent state. The database is unavailable.
Is this bad? Yes. Most definitely. This is the state where you take a deep breath and start checking when your last good backup ran.
Can I change this with a restart? No. Restarting won’t fix it. Recovery already failed once. It will fail again.
Any other notes? The most common root causes are related to storage or I/O failures, so you can blame your system admins or network admins, or maybe some vendor. Not that it would solve the problem though. Other causes include recovery failures where the data and log files aren’t in sync, logical consistency errors detected, and even availability group issues. As ever, start by reviewing the error log.
At this state your options are:
- Restore from your last known good backup. This is most frequently the right answer if you have backups that are both recent and restorable. However, if you haven’t completed a DR test in a while, this could be a resume generating event.
- If you have no recent and restorable backup (and shame shame shame on whoever let that happen), you can set the database to EMERGENCY state noted below, and then try some database surgery, which means likely data amputation.
- If repair fails too, keep the database in EMERGENCY and try to copy data out to a fresh database. But be aware that if this was caused by a storage or an I/O error, you may have a hardware problem that will corrupt the next database too if you don’t address it.
EMERGENCY
What does this mean? Somebody with sysadmin rights manually and explicitly set this state, presumably to try database surgery. The database is now marked as READ_ONLY with logging disabled. Only sysadmins can connect.
Is this bad? Well, a human (or maybe your new AI Agent?) did this, and that’s almost always because something else already went wrong. The EMERGENCY state itself isn’t the problem, since the database was already broken.
Can I change this with a restart? No, restarting won’t get you out of EMERGENCY. You have to either run a successful repair, restore from backup, or try to manually set it back with ALTER DATABASE.
Any other notes? EMERGENCY exists for two reasons: to let you read data out of an otherwise dead database, and to allow DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS to run. The name of that option tells you everything you need to know about how it works, so that option should be a last resort. Microsoft explicitly says it can lose more data than restoring from a known good backup.
If you find yourself reaching for EMERGENCY mode in production, the real problem is that you didn’t have a usable backup, and that’s the lesson to take away. Now might be a good time to try out our free sp_CheckBackup tool to find out what’s up with your database backups.