sql server database status DataBasePropertyEX
The SQL Server database is always in one specific state. You can check the current state of single database using the DatabasePropertyEx('DATABASE_NAME', 'Status') builtin function. You can also query "sys.databases" "status_desc" column to get the status of all the databases.
SELECT DatabasePropertyEX('hnr', 'Status') AS [Database Status]
The following chart describes what each state means.
- ONLINE Database is available for access and usually a sign of healthy database.
- OFFLINE Database is unavailable. A database becomes offline by user action and remains offline until additional action is taken, mostly once the database is moved from one enviorment to another DBA puts the old database OFFLINE.
- RESTORING Database is restoring, during this time SQL Server database is unavailable. You will see it very often if you are using log shipping. During this time database is unavailable for access.
- RECOVERING When SQL Server is trying to recover the database. The database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. Database will not be available during recovery process.
- RECOVERY PENDING This status means SQL Server has encountered an error during recovery process. The database is not damaged at this point, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable during this time. Any user interaction might be required to resolve the error(s) and let the recovery process be completed.
- SUSPECT Atleast one of the primary filegroup is suspect and may be damaged. it can also mean the database cannot be recovered during startup of SQL Server. SUSPECT databases are not available for access. Additional steps must be taken by the DBA/user to resolve the problem.
- EMERGENCY User or DBA has changed the database status to emergency. This will change database to single-user mode and may be repaired or restored. The database is marked READ_ONLY, all kind of logging is disabled, and database access is limited to members of the sysadmin fixed server role. EMERGENCY status is used for troubleshooting purposes. As an example, a database marked as suspect can be set to the EMERGENCY state to permit the system administrator read-only access to the database.
You can change the status of database using T-SQL. To change the status to OFFLINE you can use ALTER database statement.
ALTER DATABASE Database_Name SET OFFLINE
This information is as of SQL Server 2000 and 2005. Check the books online for more information.