Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the state of SQLSERVER database, which is very detailed and has certain reference value. Friends who are interested must finish it!

Case

Two days ago, I was dealing with a customer problem when suddenly a database could not be accessed. The tables under the database cannot be viewed either. From the SSMS interface, the database is in a normal state (that is, no special status is shown after the database name). View

SYS.DATABASES looks at the status column and finds that it is ONLINE. At this time, other databases are normal, but there is a problem with this library. There must be something wrong with the database. So what's the problem? If you look closely, the database collation_name for this problem is the null value.

So that's the problem.

The database that just came online may not be able to accept connections immediately. To determine when the database can accept connections, you can query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. After the database collation returns a non-Null value, the database can accept connections.

So use the command to set the database offline, then go online immediately, and then see that the collation_name column of sys.databases becomes a non-null value. At this point, the database returns to normal.

Transition of state

The database has many states. How do they switch between these states? The following figure clearly shows the switching of each state. When I first studied the database, this picture helped me a lot.

Let me have a clear understanding of the transition of each state of the database.

ONLINE (online)

The database can run normally.

RESTORING (restoring)

The database is being restored, and when we restore the database using NORECOVERY schema, the database becomes this state

RECOVERING (recovering)

Database startup, database creation, ALTER ONLINE,RESTORE WITH RECOERY, will go through this state, REDO,UNDO and other operations. At this point, if you encounter a problem, enter RECOVERY_PENDING. If normal, it will become ONLINE.

RECOVERY_PENDING (waiting for recovery)

The database encountered resource-related errors during restore, indicating that the restore process was suspended and the database could not start the restore process of database data and logs. In this case, the most likely reason is the loss of data files or log files.

SUSPECT (doubtful)

The database may be corrupted

EMERGENCY (urgent)

Used by DBA to repair the state of the database

OFFLINE (offline)

Offline statu

The above is all the contents of the article "sample Analysis of SQLSERVER Database status". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.