These three options describe the user access property of a database. They are mutually exclusive; setting any one of them unsets the others. To set one of these options for your database, you just use the option name. For example, to set the AdventureWorks database to single-user mode, use the following code:
ALTER DATABASE AdventureWorks SET SINGLE_USER;
A database in SINGLE_USER mode can have only one connection at a time. A database in RESTRICTED_USER mode can have connections only from users who are considered “qualified”—those who are members of the dbcreator or sysadmin server role or the db_owner role for that database. The default for a database is MULTI_USER mode, which means anyone with a valid user name in the database can connect to it. If you attempt to change a database’s state to a mode that is incompatible with the current conditions—for example, if you try to change the database to SINGLE_USER mode when other connections exist—the behavior of SQL Server will be determined by the TERMINATION option you specify. I’ll discuss termina- tion options shortly.
To determine which user access value is set for a database, you can examine the sys.databases catalog view, as shown here:
SELECT USER_ACCESS_DESC FROM sys.databases WHERE name = '<name of database>';
This query will return one of MULTI_USER, SINGLE_USER or RESTRICTED_USER.
OFFLINE | ONLINE | EMERGENCY
You use these three options to describe the status of a database. They are mutually exclusive. The default for a database is ONLINE. As with the user access options, when you use ALTER DATABASE to put the database in one of these modes, you don’t specify a value of ON or OFF—you just use the name of the option. When a database is set to OFFLINE, it is closed and shut down cleanly and marked as offline. Any snapshots for the data are automatically dropped. The database cannot be modified while the database is offline. A database cannot be put into OFFLINE mode if there are any connections in the database. Whether SQL Server waits for the other connections to terminate or generates an error message is determined by the TERMINATION option specified.
The following code examples show how to set a database’s status value to OFFLINE and how to determine the status of a database:
ALTER DATABASE AdventureWorks SET OFFLINE; SELECT state_desc from sys.databases WHERE name = 'AdventureWorks';
A database can be explicitly set to EMERGENCY mode, and I’ll explain why you might want to do that after I discuss the database status values that cannot be set.
As shown in the preceding query, you can determine the current status of a database by examining the state_desc column of the sys.databases view. This column can return status values other than OFFLINE, ONLINE, and EMERGENCY, but those values are not directly settable using ALTER DATABASE. A database can have the status value RESTORING while it is in the process of being restored from a backup. It can have the status value RECOVERING during a restart of SQL Server. The restore process is done on one database at a time, and until SQL Server has finished restoring a database, the database has a status of RECOVERING. If the recovery process cannot be completed for some reason (most likely because one or more of the log files for the database is unavailable or unreadable), SQL Server gives the database the status of RECOVERY_PENDING. Your databases can also be put into RECOVERY_ PEND- ING mode if SQL Server runs out of either log or data space during rollback recovery, or if SQL Server runs out of locks or memory during any part of the startup process. I’ll go into more detail about the difference between rollback recovery and startup recovery in Chapter 5. If all the needed resources, including the log files, are available, but corruption is detected during recovery, the database may be put in the SUSPECT state. You can determine the state value by looking at the state_desc column in the sys.databases view. A database is completely unavailable if it’s in the SUSPECT state, and you will not even see the database listed if you run sp_helpdb. However, you can look at the DATABASEPROPERTYEX values of a suspect database and see its status in the sys.databases view. In many cases, you can make a suspect database available for read-only operations by setting its status to EMERGENCY mode. If you really have lost one or more of the log files for a database, EMERGENCY mode allows you to access the data while you copy it to a new location. When you move from RECOVERY_ PENDING to EMERGENCY, SQL Server shuts down the database and then restarts it with a special flag that allows it to skip the recovery process. Skipping recovery can mean you have logically or physically inconsistent data—missing index rows, broken page links, or incorrect metadata pointers. By specifically putting your database in EMERGENCY mode, you are acknowledging that the data might be inconsistent but that you want access to it anyway.
Emergency Mode Repair
You can run the DBCC CHECKDB command while in EMERGENCY mode, and when you specify the REPAIR_ALLOW_DATA_LOSS option, SQL Server can perform some special repairs on the database, which may allow for ordinarily unrecoverable databases to be made physically consistent and brought back online. These repairs should be used as a last resort and only when you cannot restore the database from a backup.
When the database is set to EMERGENCY mode, the database is internally set to READ_ONLY, logging is disabled, and access is limited to members of the sysadmin role.
However, the properties of the database that you see in sys.databases will not reflect these restrictions.
When the database is in emergency mode and DBCC CHECKDB with the REPAIR_ALLOW_ DATA_LOSS clause is run, the following actions are taken:
■ DBCC CHECKDB uses pages that have been marked inaccessible because of I/O or checksum errors, as if the errors have not occurred in order to increase the chances for data recovery.
■ DBCC CHECKDB attempts to recover the database using regular log-based recovery techniques.
■ If database recovery is unsuccessful, the transaction log is rebuilt. Rebuilding the trans- action log may result in the loss of transactional consistency.
If the DBCC CHECKDB command succeeds, the database is in a physically consistent state and the database status is set to ONLINE. However, the database may contain one or more transactional or logical inconsistencies. You should consider running DBCC CHECKCON- STRAINTS to identify any business logic flaws and immediately back up the database. If the DBCC CHECKDB command fails, the database cannot be repaired.
In some cases, EMERGENCY mode is not possible, in particular if some of the metadata related to space allocation, which is needed to start up the database, is missing or corrupt. You can attempt to set a database that is in EMERGENCY mode into ONLINE mode (if the missing files have been made available, for example), and SQL Server will try to run recovery on the database. If the transition to ONLINE cannot be completed, the database will be left in either RECOVERY_PENDING or SUSPECT status, just like when you first bring up your SQL Server instance and try to recover the database. Once again, you can change the state of the RECOVERY_PENDING database to EMERGENCY mode to allow the data to be read. It’s relatively easy to test emergency status value for a database on a test server. You can create a simple database with the three-word command CREATE DATABASE TESTDB, and then stop your SQL Server instance and rename (or remove) the log file. When you restart your instance, check the status of the new database:
SELECT name, database_id, user_access_desc, state_desc FROM sys.databases
WHERE name = 'testdb';
The state_desc should show RECOVERY_PENDING, which you can now change to EMERGENCY:
The database will now be available for reading data, even though there is no transaction log. If you try to update the database in any way, you’ll get the following error:
Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'testdb' because the database is in bypass recovery mode.
The statement has been terminated.
If you try to set the database state back to ONLINE, you will get an error indicating that recovery is not possible, and the database will be put back in RECOVERY_PENDING mode. As previously mentioned, running DBCC CHECKDB with the repair option while in EMERGENCY mode can put the database back in ONLINE mode if the database can be repaired.