In SQL Server, A database is in single-user mode and ready to be repaired or restored when it is in which of the following states?
- EMERGENCY
- OFFLINE
- RECOVERY PENDING
- SUSPECT
EXPLANATION
Database State Definitions
The following table defines the database states.| State | Definition |
|---|---|
| ONLINE | Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed. |
| OFFLINE | Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed. |
| RESTORING | One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable. |
| RECOVERING | Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable. |
| RECOVERY PENDING | SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed. |
| SUSPECT | At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem. |
| EMERGENCY | User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state. |
Database State Definitions
The following table defines the database states.| State | Definition |
|---|---|
| ONLINE | Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed. |
| OFFLINE | Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed. |
| RESTORING | One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable. |
| RECOVERING | Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable. |
| RECOVERY PENDING | SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed. |
| SUSPECT | At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem. |
| EMERGENCY | User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state. |
The database can be changed to the EMERGENCY state by a sysadmin user
action, in order to safely perform database maintenance or for
troubleshooting purposes. In this state, the database will be in
single-user mode to be repaired or restored, marked as READ_ONLY where
you can export the data out of the database, logging is disabled and the
access is restricted only to the sysadmin role members.
Let us get back again to the previous SuspectDBDemo corrupted database that is marked as SUSPECT. In order to troubleshoot its problem and resolve it, we will change the database state to EMERGENCY, permitting the sysadmin users read-only access to that database. The below ALTER DATABASE statement is used to set the database state to EMERGENCY:
Let us get back again to the previous SuspectDBDemo corrupted database that is marked as SUSPECT. In order to troubleshoot its problem and resolve it, we will change the database state to EMERGENCY, permitting the sysadmin users read-only access to that database. The below ALTER DATABASE statement is used to set the database state to EMERGENCY:
ALTER DATABASE SuspectDBDemo SET EMERGENCY
GO
0 comments:
Post a Comment