SQL Server database startup and user database Loading

SQL Server database startup and user database Loading

Preface

This article is a supplement to the previous article. In the previous article, we introduced some problems and solutions encountered during the SQL Server service startup process. Click here to view details, this article mainly introduces the User Database loading process during SQL Server startup, and provides solutions based on a series of problems encountered during the loading process.

In fact, as an excellent RDBMS of Microsoft, SQL Server has very few problems with the system libraries during its startup. We usually use it, most of the problems are caused by our own user databases.

In addition, we are more concerned with our own user databases. If there is a problem with the system database or even an instance, we will rebuild the environment in the worst case, however, if the user database of our application breaks down, it cannot be solved by rebuilding the environment. This involves the interests of the company. The seriousness of the problem is self-evident!

As we enter the topic of this article.

 

In the previous article, we introduced the SQL Server instance Startup Process and analyzed the detailed process. In this first-class process, there is a key step: loading and restoring the user database, we will intercept this log information:

 

The above is a process for starting each user database normally. SQL Server starts the database with multiple threads and performs consistency check during this process to ensure that the started database can be used normally.

Many problems may occur in this process. Before analyzing the problem, I should first introduce several common statuses of the SQL Server database:

RECOVERING ):

This status indicates that the data is being restored after it is started, that is, the rediscovery process in the preceding log, which is the same as that in other relational databases, SQL Server writes transaction logs to all database behaviors first, then modifies the data in the memory, and then writes the data to the hard disk (Lazy write) as appropriate through a background process ), therefore, when the database is running, the data in the disk is not the latest. If it is disabled at this time, the SQL Server will record the data in the transaction log during the next startup, rewrite the old data in the disk as follows:

1. redo

2. Roll back and undo/rollback

The above objective is to ensure Database Consistency.

If a problem occurs in the above process, it will go to the following status:

Recovery pending (PENDING restoration ):

This process suspends the data recovery process. The reason for suspension is that the database files used cannot be opened normally. Remember this status first. I will reproduce this problem in the following content and provide a solution.

If the file can be found or opened, but the file has a problem, the following status appears:

SUSPECT (question ):

This status, I believe that many users will encounter it occasionally when they have been playing the database for a long time. Compared with other statuses, this status is the highest.

The reason is simple: the database file is broken.

After the above statuses, the databases will not be available and enter the following status:

ONLINE ):

This status should be the most anticipated. The database is online and in normal use. By default, it is in normal online status.

 

Of course, in addition to the database status formed by the above databases, the status will be changed when our administrator processes the database. Here we will mention it by the way:

OFFLINE

RESTORING: The status is very simple. The Administrator is RESTORING the database.

EMERGENCY (urgent): this status is also used by the Administrator. It indicates that the database has a problem and it is trying to solve it.

 

The above statuses occur during startup, and the above issues occur: recovery pending (PENDING restoration), SUSPECT (questioning), and RECOVERING (RECOVERING ):

Let's look at it in sequence:

Recovery pending (PENDING restoration ):

The common cause of this status is that the database file cannot be found, or the file cannot be accessed by the permission. The error message is as follows:

In the database storage mode, there are master file groups, auxiliary file groups, and log files. For ease of display, we have created a Test Library to reproduce this problem:

 

 

<1> primary file group Problems

When you cannot access the main file group file, that is, the above CnblogsTestDB. mdf file, the following error is reported:

Let's first look at the database:

During the instance startup process, there happened to be a database that showed the status we mentioned above: RECOVERING (RECOVERING). I gave the figure by the way. Of course, this situation is normal, sometimes, refresh is normal. Other user databases are not displayed because the database is too small and the recovery time is too short. We cannot capture it.

We can see that the test library CnblogsTestDB we created above is no longer accessible. Let's take a look at the Error message in Error:

The error message is obvious. If the file cannot be accessed and the file is an operating system error, check the error record of the operating system:

You can see this part of the error message in the Windows system log.

Solution:

The solution to this problem is simple. Generally, it is mainly because of permission issues. You only need to grant the database administrator account group the permission to read/write permissions, and then restart the service:

 

In the above case, the database file is found, but the database file cannot be opened. Of course, the database file cannot be directly found, and the system reports the following error:

A 17204 error is returned, and a file error cannot be found.

Solution:

A. If you can find the data file, copy it to the wrong path and restart the instance.

B. If the file cannot be found, you must delete the database, create a new database with the same name, and restore the database from the backup file.

Generally, the above problems occur in physical storage. Of course, some software operations, such as anti-virus software and manual accidental deletion, are not excluded. If there is no backup, this may be a big problem. It is basically possible to determine that the possibility of a full restore is not high! So remember: the importance of backing up the database!

 

<2> auxiliary file group Problems

The above problematic file is the primary file group of the database. When our database is carrying a certain amount of data, how can I use multiple secondary file groups to hold data, let's take a look at the problem of the secondary file group:

The secondary file group with the same prompt cannot be opened normally, or the relevant secondary file group cannot be found. How can we solve this problem?

In fact, the auxiliary file storage of the SQL Server database is mainly the data content information of the database. Some architecture information about the database is stored in the primary file group, so we can first

Solution:

A. Set the database files that cannot be opened or cannot be accessed (auxiliary files) to offline, and then publish the normal data files first, make sure that the information of other libraries except the damaged files can be accessed normally. Use the following code to change the information:

ALTER DATABASE CnblogsTestDB MODIFY FILE(NAME=CnblogsTestDB2,OFFLINE)
GO
ALTER DATABASE CnblogsTestDB set ONLINE
GO

In this way, we can refresh the database to normally access the correct data information:

When we are in a production environment and the production database cannot be started normally, the above method is adopted to ensure that some data can be accessed normally at the time of the emergency.

The following steps are to find the secondary file and ensure that you have normal access permissions. More importantly, the secondary file cannot be damaged, and then copy it to the path given in the error file, restart the instance and launch the database.

B. Of course, in most cases, we cannot find the file, or the file is damaged, so we have to adopt the second solution to restore it through backup. Based on past experience, the recommended measures are as follows:

Back up the database that can be accessed, and restore the problematic file group through the file group.

For more details, please continue to read the highlights on the next page:

1 2 3 Next