THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Maintain Database Partial Availability

In SQL Server 2005, the basic unit of availability is filegroups. A database can remain online as long as the primary filegroup and the transaction log are online. In addition, if a data file is damaged or missing, only its user-defined filegroup becomes unavailable; all the other user-defined filegroups remain online. This ability to keep a database partially available is an especially important feature in managing very large databases.


To take advantage of partial database availability, the database must include one or more user-defined filegroup.


While the database is online and a secondary data file becomes damaged or missing, the status of the database remains online. You can run the following T-SQL statements to view the current status of the database and its files.

SELECT * FROM sys.databases

SELECT * FROM sys.database_files


In the system catalog view sys.database_files, the state of the affected secondary data file is still shown as online, although it is unavailable thus effectively offline at this point. The Windows eventlogs and the SQL Server errorlog should have error messages related to the file damage. You can use the error messages in the SQL Server errorlog or the Windows eventlogs for monitoring purposes.


However,  any attempt to access an object in the filegroup with a damaged or missing data file will result in an error message similar to the following where the table name is userTable:

Server: Msg 8653, Level 16, State 1, Line 1

The query processor is unable to produce a plan for the table or view 'userTable' because the table resides in a filegroup which is not online.


Note that database partial availability is not one of the explicitly defined database states you find in the state_desc column of the system catalog view sys.databases.


While a database is partially available, if for some reason the database is taken offline, any attempt to bring the database online with ALTER DATABASE SET ONLINE will cause the database to be in the RECOVERY_PENDING state if the secondary data file is still damaged or missing. Similarly, when a secondary file becomes damaged or inaccessible, and then the SQL Server instance is stopped, restarting the SQL Server instance will cause the database to be in the RECOVERY_PENDING state. In either case, the entire database becomes unavailable.


At this point, how would you go about achieving partial database availability?


Once the database is stuck in the RECOERY_PENDING state, before you can bring the database online without the damaged or missing secondary data file, you must first set the state of the damaged data file to offline. The following example sets a data file named damagedFile in database userDB offline:



Note that once a file is set offline, the only way to set the file online is by restoring the file from backup. See “ALTER DATABASE MODIFY FILE” in Books Online for more information.


Now that the damaged or missing file is set to offline, the database can be brought online as follows:



At this point, the database is online and partially available. Any object in the filegroup of the damaged or missing file is not available. However, the objects in the other filegroup(s) are now available.


A database may become partially available because a secondary data file is missing but not damaged. One practical scenario is when a disk presented from a SAN is disconnected from the host. SQL Server may not immediately discover that a secondary data file is missing, and may allow operations on the objects in the affected filegroup to continue. However, any operations that cause physical I/Os to the secondary data file will fail. CHECKPOINT is an example that will fail.


Once the missing secondary data file becomes available (e.g. when the disk from the SAN is reconnected back to the host) and is undamaged, the database will become fully available without any user intervention or after the SQL Server instance is restarted.

Published Monday, February 26, 2007 6:00 PM by Linchi Shea
Filed under: ,



Bjorn said:

Hi I took an datafile offline for moving purpose,

but now I can't get it online again using alter database - help!


May 22, 2007 11:04 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement