Database
can always be in any one of the specific state.
To
identify the current database state, this script can be used.
>> SELECT
state_desc
from SYS.DATABASES where name = 'EmployeeDB'
-- ONLINE
Database
States can be categorized into
1. ONLINE
Ø
The
primary file group status is online. It is available for access to users.
2. OFFLINE
Ø
Database
is not available for user access. Once Database becomes offline state, it
requires user action to bring it online back.
Ø
Usually
while we are moving database files, we make database state to offline and move
the database files to specific location and change the database state to
online.
3. RESTORING
Ø
Database
is not available for user access.
Ø
In
this stage, one or more primary file group files are in the process of
restoring.
4. RECOVERING
Ø
Database
is not available for user access.
Ø
It
is in the process of database recovery state [Transient State].
Ø
If
recovery is successful, database will automatically come to online state. If
recovery is not successful, then database will go to suspect mode.
5. RECOVERY PENDING
Ø
Database
is not available for user access.
Ø
It
means, database is not able to come to online state due to some resource
related error which means database is not damaged but one or more files are
missing or Resource limitation of system is preventing database to start etc..
Ø
It
requires user attention to resolve the issue and let the database to complete
the recovery process.
6. SUSPECT
Ø
Database
is not available for user access.
Ø
It
means, database recovery process is unsuccessful. It may be because of primary
file group is damaged.
Ø
Database
cannot be recovered during the start-up of SQL Server. It requires user action
to resolve the issue.
7. EMERGENCY
Ø
Database
is available for READ_ONLY to sysadmin user.
Ø
It
is mainly used for troubleshooting purpose.
Ø
If
database is in suspect mode, it cannot be accessible to anyone. In this case
sysadmin can change its state from suspect to EMERGENCY. So that sysadmin will
get READ_ONLY access to database to troubleshoot and fix the issue.
Query
to make database ONLINE\OFFLINE
Below
queries can be used to make database ONLINE\OFFLINE.
>> USE [master]
>> ALTER DATABASE <<DB Name>> SET
OFFLINE WITH ROLLBACK IMMEDIATE
>> ALTER DATABASE <<DB
Name>> SET ONLINE
No comments:
Post a Comment