What you don't know about the SQL Server database startup process and various questions about inaccessibility

Currently, as an excellent RDBMS of Microsoft, SQLServer database has very few problems when it is started. When we use it at ordinary times, we seldom pay attention to the startup process, or you rarely know the underlying running process. Most of the processes only focus on a series of application methods such as internal tables, stored procedures, views, and functions. One day, when it runs normally

Currently, as an excellent RDBMS of Microsoft, the SQL Server database has very few problems when it is started. When we use it at ordinary times, we seldom pay attention to the startup process, or you rarely know the underlying running process. Most of the processes only focus on a series of application methods such as internal tables, stored procedures, views, and functions. One day, when it runs normally

Currently, as an excellent RDBMS of Microsoft, the SQL Server database has very few problems when it is started. When we use it at ordinary times, we seldom pay attention to the startup process, or you rarely know the underlying running process. Most of the processes only focus on a series of internal applications such as tables, stored procedures, views, and functions, however, one day when it runs normally, it suddenly fails to be started. At this time, it is helpless. What it can do may only be Reinstallation, configuration, and restoration, however, this process is actually a very time-consuming process, especially when we are faced with a large production database, it may be at this very pressing moment, you cannot re-build an environment.

Therefore, as a qualified database user, we need to understand the startup and running processes of the database. Once a problem occurs, we can promptly locate and solve the problem.

Let's start with the topic in this article.

SQL Server stores a Windows service, and each server corresponds to a sqlserver.exe process. This is an executable file, which is stored in the installation directory of SQL Server by default. When we start the file, it is called directly and then started the service.

Part 1: SQL Server instance startup methods and Problems

SQL Server instances can be started in the following ways:

(1) manual or automatic startup (default) in the Windows service console. This is also the most common method.

(2) The second method is the startup method provided by SQL Server itself. Here we can start it manually.

(3) manually start SQL Server SSMS. This method is generally used for manual restart.

(4) In the Windows Command window, run the 'net start' command to start the instance manually.

All of the preceding methods can start SQL Sever and are recorded in SQL log information.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Part 2: detailed process of SQL Server instance startup and Related Problems

Step 1. Check the registry key

When a sqlserver.exe file starts to be started, the first thing to do is to check its configuration information for the value items stored in the registry.

The following key values are important:

Here

AuditLevel: in fact, it is how SQL records user logon records;

LoginMode: indicates the identity authentication method of the SQL Server;

BackupDirectory: default backup path and other information;

For more information about the registry, do not make any modifications. Of course, these values can be set in SQL Server by default:

If the registry is not modified, the startup sequence of this step is generally normal. Of course, there is usually no way to solve the problem, and most of the solutions are only reinstalled.

However, the following two problems can be solved in this step:

<1> account startup Permissions

If the account used to start the SQL Server process does not have the permission to read the registry, then this service cannot be started. Generally, even SQL error logs cannot be generated at this time.

How can we find out at this time? Although it cannot create SQL error logs at this time, it leaves traces on the Windows layer. Let's look at it:

Set the Service Startup account to the gust Guest account to start the service.

The following error message is generated:

An error log is also generated in the Windows Log information:

Here, access denied refers to access to the registry information.

Solution:

The solution to this problem is simple. You only need to escalate the privilege of the user to the startup account of the SQL Server service. The Elevation of Privilege is also simple, you only need to add the startup Service Group of the local user to SQL.

Of course, you can also directly log on to a higher-level user. Generally, the super Administrator account is used by default.

<2> access logs and folders are faulty.

By default, when SQL Server is started, a startup log file will be created to record all the correct log information, including the error log information. If the path of this log information cannot be found at this time, or a log already exists, but the log is locked (some anti-virus software of NB is good at doing this). At this time, this service cannot be started, and the log file of SQL Server cannot be created, at this time, we have to solve the problem by using the Windows platform itself.

The log file path created by SQL Server startup also exists in the registry key. Let's look at this parameter:

Here we intentionally change it to an incorrect path to start it:

The following error occurs:

System Error Log Information

The error description is clear.

Solution:

This problem is easy to solve. You only need to check the path to ensure that the file under the path is correct.

However, when SQL Server is not started, check the system logs on the Windows platform for some error messages.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 2: Check the System Configuration environment, including hard disk, memory, and CPU.

When we finish step 1, SQL Server has read the registry information, created its errorlog file, and then started Step 2, all the information in this step is recorded in the errorlog file in sequence. We can view the file to track this step in detail. According to the registry information in the previous step, first, manually clear the log, restart the SQL Server service, and view the log record.

We have roughly divided the following steps:

1. First, check the system software environment, including OS version, computer signal, memory, hard disk, and registry basic configuration items.

2. Start the system database master

3. Start to use service users to log on to the system, start the system resource database, and check the database version information.

4. Start the system database model

5. Start network configuration for connection and provide external services. Use the default port 1433

Analyze the following logs:

6. After completing the fifth step, start the msdb system database.

7. At this time, start the user database, complete the integrity verification of each database, and clear the tempdb of the system database before starting the user database.

8. Start another system database tempdb only after the system is built.

Detailed log records are generated during the above SQL Server system startup process. We will perform detailed analysis following this step in sequence.

There are basically no fatal errors in the process of checking the system's hardware and software environment. The most common problem is the memory configuration. In fact, the above log record is particularly important. It reflects the memory usage of SQL Server. Let's look at it:

This statement locks all data pages to the memory. For most databases, the memory is the lifeline, and the SQL Server is also the same. If the system (64 bit) when there is no memory pressure, data pages can be properly locked to the memory. If the memory pressure is too high, the system memory cannot add data pages to the memory, this causes serious performance problems of SQL Server.

Many users want to limit the memory usage of SQL Server, and some clients limit it to services that cannot be started. At this time, the SQL Server logs are shown as follows:

We can see that the cause of this error is quite clear, and the solution to this error is also very simple. You can increase the memory configuration.

The memory-related special case is that the SQL Server startup account does not have the permission to Lock page in memory on the Server, the above log records cannot be viewed in the detailed log, and the solution to this problem is also very simple. You only need to add the required permissions. The method of adding permissions is as follows:

After completing the steps above, complete the data software and hardware detection process.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 3: Start the system database master

The master database is the first system database in the SQL Server system startup process and is a critical database. If the database cannot be opened normally, SQL Server cannot be started normally.

Like other databases, the master database is also divided into data files and log files. The Startup Process is to open them in sequence and then perform restoration operations. If this process is okay, In the Errorlog file, we will see the following sentence:

If any problem occurs during this process, the SQL Server startup process will be interrupted and the startup process will fail.

The errors in this process focus on the following situations:

<1> the data file or log file of the master data cannot be found in the specified path.

The path of the most important system database of SQL Server exists in the form of a registry. In the registry entry, you can see

If the system database cannot be found in this path, the service cannot be started and the corresponding error log information will be generated. We can simulate and disable the service, remove the two files and start them:

First, the service fails to be started.

Let's take a look at the system logs.

View the log information of Errorlog

We can see that the error message displayed for this problem is quite detailed. Let's look at the second situation.

<2> the file is found, but you do not have the permission to access it, or you cannot open the file in exclusive mode (the exclusive lock is used by default to open the file)

This situation is also possible. For example, some anti-virus software of NB can do this to make your system library inaccessible, which also cannot be started. Let's see, which error messages are prompted:

Here is the error record of Errorlog:

<3> the file is found and the access permission is also available, but the file is faulty, that is, the database is damaged.

This problem also often occurs. For example, if the disk breaks down and the file is faulty after recovery, it cannot be opened normally. For this problem, let's look at the error message:

Log Information

The Startup Process of the master System database is basically the above three errors. How can we solve these three problems?

Solution: first, if you locate the nature of the problem based on the error log, if the first two problems are in fact well resolved, for example, the file is not found or the permission item is incorrect, these problems can be solved accordingly. The most difficult case is the third case. The most ideal case is that the master database is backed up and restored through the backup file, everything can be done normally. Of course, by stopping the service with brute force, copying files can also be solved.

The most worrying thing is that this database is not backed up. How can this problem be solved? To solve this problem, you must use the SQL Server Installation program to re-build the master data. However, the re-built master database will clear all the previous SQL Server settings.

Cleared information includes: all account information (meaning reconstruction), all job information in msdb (also need reconstruction), user database information (all must be re-attached to attch)

If this process is a production database, it may be a huge workload!

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 4. Start the system resource database and check the data version information

Resource Database is a logical database introduced in SQL Server2005. It cannot be seen under the instance, but its physical file exists. The default name of the primary database is mssqlsystemresource. mdf, log name: mssqlsystemresource. ldf

If a problem occurs during the database startup, the SQL Server cannot be started normally.

This system database is special. It is a read-only database and is fully maintained by SQL Server. Users cannot change it. Therefore, we only need to ensure that the database files and logs are in good condition, it does not need to be tracked or maintained.

Of course, if you have to read this database, you can connect to it through a single-user DAC.

Therefore, this database will not be accidentally started in general, but it can be started normally. However, in special cases, the following two cases cannot be started:

<1> the database file does not exist, cannot be accessed, or the file is broken.

In fact, the error message it reported is similar to the master database above. Let's take a look at it:

This is the error message recorded by errorlog.

At the windows level, it also has its own error log information:

<2> the version of the resource database is different from that of SQL Server.

This may be because the resource database is manually modified, resulting in inconsistency between the existing resource database files and the database version. In this case, errors may occur.

The windwos platform also records the error information. See the following picture:

Solution:

The solution to these two problems is very simple. Just find the database that is consistent with the SQL Server version on this Server and copy it.

Of course, the best prevention measure is: every time the SQL Server is installed or the patch is installed, the two files will be backed up in a timely manner, put in a safe place, and copy them when used, backup is the duty of the database administrator

Of course, sometimes in an emergency, the database of the same version cannot be found. In theory, the database is read-only, so it will not change. We can find a machine and install the database of the same version, copy the file. Of course, note that the version is the same.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 5. Start the system database model

The model system database is also a critical database used during SQL Server startup. If the database is damaged, SQL Server startup will also fail, the reason why model data cannot be started is basically the same as that of the master. There are also two types: 1. database files are not available or cannot be accessed; 2. database files can be accessed but are corrupt files.

You can diagnose this problem in the same way as the preceding two methods. Check the errorlog file or windows System Log generated during the startup process. We will not reproduce this problem here.

We only provide solutions to this problem:

1. If the database has been backed up, the most direct and effective solution is direct restoration. The restoration method here may be different from that of the common database, because the SQL Server instance has not been started, the recovery process is as follows:

A. Start SQL Server with parameters and execute the following command in the command prompt line. In this case, SQL Server will skip the restoration step of the model database.

net start MSSQLSERVER /f /m /T3608

B. Restore the model database now. Open SSMS and enter

RESTORE DATABASE model FROM DISK ='G:\data\model.bak'WITH MOVE 'modeldev' TO 'E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf'MOVE 'modellog' TO 'E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.ldf',replace

C. After the restoration is successful, you can directly restart SQL Server.

2. Disable SQL Server and copy the model data file back in a brute-force manner. This method is simple and effective, but not conventional.

3. Another method is to use the setup Installation File to rebuild the database. The process is slow, slightly complex, and not recommended.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 6. Start the network configuration for connection to provide external services, and use the default port 1433

After several important system libraries have been started, the next step is to check the network environment, configure network services, and provide external services. Generally, there are three network start protocols used in SQL Server: Shared Memory, Named Pope, and TCP/IP. In fact, the most common method in daily life is TCP/IP, port 1433 is enabled by default.

Let's take a look at the detailed logs of this part during normal startup:

The Shared Memory here is a dedicated connection to SQL Server through the LPC (Local Procedure Call) technology. It does not go through the network layer, so it is the fastest connection method. After normal startup, the above normal logs are displayed.

The above logs are displayed when Named Pipe is started normally. You can see.

In this case, we usually use the most common TCP/IP method, which can also be started normally, and two access methods are specified, ipv4/ipv6, followed by the 1433 port number.

The most common problem in this process is that port 1433 is occupied by other programs, which causes the TCP/IP protocol to fail to start normally. The following log information is displayed:

In addition, windows system logs are recorded.

Solution:

In fact, the problem here is quite easy to solve. You only need to find the application that occupies this port and take measures to let it out the port.

Of course, when these problems occur, it means that the client can no longer access the client through a remote connection such as TCP/IP.

At this time, the administrator can use the "dedicated administrator connection" (DAC) provided by SQL Server to connect. We will introduce this method later.

Of course, this kind of network problem occurs during SQL Server startup, or the Protocol cannot be loaded successfully, SQL Server reports an error message, however, it generally does not affect the normal startup of SQL Server. The affected protocol functions may be problematic.

We only need to locate the problem according to the log, solve it, and restart it.

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 7. Start msdb System Database

About msdb, the system database is arranged close to the last one in the system database. Besides the user database and the temporary database tempdb, when this step has been completed during the startup process, in fact, our instance has been started and can be connected.

We know that the main information stored in the msdb database is the backup information of each database of the application, and the historical running batch information of various jobs, in fact, a lot of objective data comes from the user database.

Let's take a look at the symptoms of this database:

I removed the library file and restarted the service. During the startup process, no error was reported and the service was successfully started. We directly connected the database using SSMS, and the database can be connected normally.

However, when we click open data, we don't actually see any user database, and an error message will be generated:

It seems that it cannot be used. Let's check the error log:

Although the importance of this database is a little less important than that of the master database, our SQL Server cannot be used even though it can be started without it.

Solution:

There are actually many ways to solve this problem, because our SQL Server instance can be started properly now, we can take:

1. Use Backup to restore the database. Refer to the method mentioned above (recommended)

2. Disable the service and use the violent copy file method for recovery. This is simple, effective, and unconventional operation.

3. Find the same environment, find the same file, and copy it directly.

4. Use the installation file for recovery (not recommended)

------------------------------------------------------------ Domineering split line -----------------------------------------------------------------------

Step 8. Start the user database, complete the integrity verification of each database, and clear the tempdb of the system database before starting the user database.

The problems encountered in this step are endless. For various styles, I plan to re-organize an article to list them. I will not introduce this article.

But one thing to remember: before this step, SQL Server will clear the system database of tempdb. That is to say, the system will clear tempdb every time it restarts, and then recreate it, in this step, the following log information is displayed after the operation is successful:

Step 9. Start rebuilding another database, tempdb.

The tempdb database is special. It is re-created every time it is restarted. SQL Server creates the database based on the information recorded in the master database in the model database version. Therefore, as long as we ensure that the model database is normal and the hard disk is normal, the database file of tempdb should be normal.

All configuration information about the temdb database is stored in the master database, and the content information is stored in the model system database.

This leads to a problem. Sometimes our master database is backed up from another machine, so it records the path of the tempdb database on the original machine, in this way, an error is reported during creation.

Therefore, we need to execute the following command to change the library path.

A. Start SQL Server with Parameters

net start MSSQLSERVER /f  /m  /T3608

B. Modify the path of the data file and Log File

ALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,FILENAME='C:\right path....\temdb.mdf');goALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,FILENAME='C:\right path....\temdblog.ldf');go

C. You can start the database normally.

Another case is that when the file is created, the hard disk space provided is insufficient or the permissions are insufficient. We also modify the file to a correct path based on the above method, make sure that the permission is correct.

You can also change the temp file size. The default value is 4 MB. The Code is as follows:

ALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,SIZE=100MB);goALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,SIZE=100MB);go

So far, if there is no problem in the above process, a normal SQL Server can be started successfully.

Conclusion

This article ends now ..... this article takes three days ..... in order to present all the problem phenomena as much as possible, I have carried out a variety of relentless actions and various damages on the local SQL Server, and strive to re-display the problem phenomena in different application scenarios, then try to find a suitable solution. Of course, there are still many situations that are not displayed. We will add them one by one in the future. Of course, if there are any problems that cannot be solved, you can leave a message, let's analyze and solve it together.

As for the user database startup process, this process is a step that is prone to problems, such as questions, recovery, and unavailability, which will be listed and analyzed in my subsequent articles.

The associated articles of this article have been added:

What you don't know about the SQL Server database Startup Process (difficult and difficult for the user database loading process)

If you read this blog and feel that you have gained some benefits, please do not mean your"Recommendation".