Detailed description of SQL Server database status and file status, SQL Server

Detailed description of SQL Server database status and file status, SQL Server

Database status (database states)

Query the current status of the database:

1. query the status of all databases by using the state_desc column in the sys. databases directory View

user mastergoselect state_desc ,[name] from sys.databasesgo

2. query the Status of the specified database by using the Status attribute of the DATABASEPROPERTYEX function.

select DATABASEPROPERTYEX('demoData','status')go

Status:

ONLINE: You can access the database. The master file group is still in the online status even if it may not have been restored.

OFFLINE: the database is unavailable. The database is offline due to explicit user operations and remains offline until other user operations are performed. For example, the database may be offline to move files to a new disk. Then, after the mobile operation is completed, the database is restored to the online state.

RESTORING: one or more files in the primary file group are being restored, or one or more auxiliary files are being restored offline. The database is unavailable.

RECOVERING: RECOVERING the database. The restoration process is a temporary state. After the restoration is successful, the database is automatically online. If the recovery fails, the database is in a suspicious state. The database is unavailable.

Recovery pending: the SQL Server encountered a resource-related error during RECOVERY. The database is not damaged, but files may be missing or system resource restrictions may cause the database to fail to be started. The database is unavailable. You must perform other operations to solve the problem and complete the recovery process.

SUSPECT: at least the master file group is suspicious or may be damaged. The database cannot be recovered during SQL Server startup. The database is unavailable. You need to perform other operations to solve the problem.

EMERGENCY (urgent): the user changes the database and sets its status to EMERGENCY. The database is in single-user mode and can be repaired or restored. Mark the database as READ_ONLY, Disable Logging, and only access to members of the sysadmin fixed server role. EMERGENCY is mainly used for troubleshooting. For example, you can set a database marked as "suspicious" to the EMERGENCY state. This allows the system administrator to perform read-only access to the database. Only members of the sysadmin fixed server role can set the database to the EMERGENCY state.

File status (database states)

In SQL Server, the database file status is independent of the database status. The file is always in a specific State, such as ONLINE or OFFLINE

Query the File status:

-- To view the current state of the file, select state_desc, [name] from sys. master_filesselect state_desc, [name] from sys. database_files -- if the database is offline select state_desc, [name] from sys. master_files

ONLINE: files can be used for all operations. If the database is online, the files in the main file group are always online. If the files in the main file group are offline, the database is offline and the auxiliary files are undefined.

OFFLINE: files are not accessible and may not be displayed on the disk. Files are changed to offline by explicit user operations, and remain offline before other user operations are performed.

** Warning ** \ * When the file is damaged, the file should only be set to offline, but can be restored. Files set to offline can only be set to online after being restored from backup.

RESTORING: RESTORING the file. The file is in the restored State (because the restore command affects the entire file, not only page restore), and remains in this State until the restoration is complete and the file is restored.

Recovery pending: file RECOVERY is postponed. Because the file is not restored or restored during the paragraph restoration process, the file automatically enters this state. You need to perform other operations to solve the error and allow the restoration process to be completed.

SUSPECT: failed to restore the file during online restoration. If the file is in the master file group, the database also marks it as suspicious. Otherwise, only the file is in the suspicious state, and the database is still in the online state.

The file remains suspicious until it is available in one of the following ways:

Restore and restore

Bcc checkdb containing REPAIR_ALLOW_DATA_LOSS

DEFUNCT: the object is deleted when it is not online. After an offline file group is deleted, all files in the group become invalid.

Summary

The above is a brief introduction to the SQL Server database status and file status. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!