SQL Server Database States

In my daily job as a Technical Consultant I regularly work with  NAV and the SQL databases it’s using . I’ve seen (or set SQL) databases in the following states (in SQL Server):

Read-Only The database can be set to this mode if the data shouldn’t be updated.
Single User In single user mode only one user is allowed acces. In some situations it’s neccesary to perform maintenance tasks in Single mode.
Restricted User In restricted user mode only users with the sysadmin or dbcreator role  can access the database.
Offline Database is not available for access.
Emergency In Emergency mode you can use DBCC CHECKDB to bring the database back online again.
Recovery Pending If for example the logfile is damaged or corrupted, the database will be in this state. Action by the DBA is needed to resolve the error.

In SSMS2016 (SQL Server Management Studio 2016):

Database States in SQL 2016

Note: this is not an exhausting list of all the states. My advice is to check the File States Article on MSDN  for more information. Please also use Google for more advanced information about other database states like ‘Suspect’,’Recovery Pending’ etc.

For some states to enable I’ve used TSQL. Below you can find them:

CREATE DATABASE [Demo Database NAV (9-0) DB2]
ALTER DATABASE [Demo Database NAV (9-0) DB2] SET SINGLE_USER

CREATE DATABASE [Demo Database NAV (9-0) DB3]
ALTER DATABASE [Demo Database NAV (9-0) DB3] SET RESTRICTED_USER

CREATE DATABASE [Demo Database NAV (9-0) DB4]
ALTER DATABASE [Demo Database NAV (9-0) DB4] SET Offline

CREATE DATABASE [Demo Database NAV (9-0) DB5]
ALTER DATABASE [Demo Database NAV (9-0) DB5] SET Emergency
Advertisements

Author: Ibrahiem Rasoelbaks

I'm a Technical Consultant working at 4PS.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s