The SQL Server database startup process you don't know, and the analysis and resolution techniques for various issues that don't start up

At present, SQL Server database as a good Microsoft RDBMS, its own start-up time is very few problems, we usually use the time, very little attention to the startup process, or very little understanding of its underlying running process, most of the process only focus on its internal tables, stored procedures, views, Functions such as a series of applications, and when it is normal to run a day, and suddenly start not up, at this time there is no way, can do can only be re-installed, configuration, restore, etc., but this process is actually a very time-consuming process, especially when we face is a huge production library, it may be in this burn moment , you are not allowed to re-establish a set of environment.

So as a qualified database users, we want to understand its start, the operation of the process of things, once a problem, we can also locate and quickly resolve.

Gossip, we enter the subject of this article.

SQL Server itself is a Windows service, and each instance corresponds to a sqlserver.exe process. This is an executable file, the default is placed in the SQL Server installation directory, when we start, it is to call this file directly, and then start the service.

The first part, the method that the SQL Server instance starts, and the issues that occur when you start

The SQL Server instance is divided into the following startup methods:

(1) Start manually in the Windows Service console, or start automatically (default), this is the most common way

(2) The second way is SQL Server itself provides a way to start, we can manually start

(3) Start it manually in the SQL Server SSMs, which is mostly done manually with this method

(4) With the Windows Command window, the ' net start ' command is started manually, this method can also be used

All of these methods can start SQL Sever and will be recorded in the SQL log information.

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

The second part, the detailed process of SQL Server instance startup, and the problem item that occurred

The first step, check the registry key

When a Sqlserver.exe file starts, the first thing to do first is to check that its configuration information is stored in the registry value entry

There are several key values that are more important:

Here's

AuditLevel: In fact, it is how SQL records user login records;

LoginMode: Is the SQL Server authentication method and so on;

BackupDirectory: The default backup path and other information;

For a quick overview of the registry information, it is not recommended to make any changes, but the information of these values can be set by default in SQL Server:

In the case of not modifying the registry, generally this step of the boot order generally do not have problems, of course, there is usually no way to solve the problem, most of the solution is only re-installed.

However, the following two issues are usually resolved in this step:

<1> Start Account Permissions issues

If we start the process of SQL Server using an account that does not have access to the registry, then this service is not able to start, usually at this time even the SQL error log is not capable of generating.

How do we find out at this point, although it is not able to create the SQL error log at this time, but it leaves a trace on the Windows level, we see:

I set the service startup account to gust Guest account to start the service

The following error message is generated:

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

The denial of access here means denying access to the registry information.

Workaround :

The solution to this problem is very simple, just need to take the user of course to the SQL Server service startup account on the line, the right way is very simple, only need to add to the SQL local user's Startup service group can be.

Of course, you can also switch directly to a higher-level user login. The Super Administrator account is usually used by default.

<2> access logs and folders are having problems

By default, when SQL Server starts, it creates a startup log file, logs all the correct log information, and of course includes the wrong log information, if the log information is not found at this time, or there is a log, but the log is locked (some NB antivirus software is good at doing this), At this time the service is also unable to start, but also the creation of SQL Server log files, this time we have to rely on the Windows platform itself to solve.

The SQL Server-initiated log file path, which also exists in the registry key, we look at this parameter:

Here we deliberately changed to a wrong path, to start the next look:

The following error is generated

Error log information for the system

The error description is very clear.

Workaround :

This problem is easy to solve, just check the path and make sure the file is correct on the path.

One thing to note, though, is that when SQL Server is not up, there are some error message logs that need to be checked for system logs under the Windows platform.

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Second Step, check the system configuration environment, including hard disk, memory and CPU, etc.

When we finish the first step, SQL Server has read the registry information, completed its errorlog file creation, and then started the second step, all the information will be sequentially recorded in the Errorlog file, We can look at the file to track this step in detail, according to the registry information in the previous step, we will first manually clear the log, and then restart the SQL Server service to view the next log record

We have briefly divided the following major steps:

First, check the system's software environment, including OS version, computer signal, memory, hard disk, registry basic configuration items are correct, etc.

Second, start the system database master

Third, start to use the service user login system, start the system resource database, check the database version information, etc.

Iv. starting the System Database model

V. Start the network configuration to connect, provide services externally, use the default 1433 port

We then analyze the following log:

In fact, after completing the fifth step above, we started to start the msdb system database.

Seven, the time to start a real start user database, and complete the integrity of each library check, and before starting the user database, the system library of tempdb to empty first

Eight, after the completion of the construction, the system started to start another database tempdb

The entire SQL Server system above starts up with verbose logging, and we'll follow that step in more detail as we step through the process.

In the process of checking the system hardware and software environment, there is basically no fatal error. The more common problem is the memory configuration problem, in fact, in the above log record has a particularly important, it reflects that SQL Server uses the memory situation, we see:

This means that all the data pages locked into memory, as most of the data, memory is the lifeline, SQL Server is also, if the system (64bit) without memory pressure in the case of the data page can be normal locking into memory, if the memory pressure is too large, System memory is not allowed to add data pages to memory, which can cause problems with SQL Server's serious performance problems.

Many users want to limit the use of SQL Server memory, and some clients restrict it to a situation where the service is not able to start, which is shown in the SQL Server log, let's see:

As you can see, the cause of the error is quite clear, and the workaround for fixing the error is simple, and the memory configuration can be made larger.

There is a special case related to memory, that is, the SQL Server startup account does not have the lock page in memory permissions on the server, if not, in the detail log to see the above log records, the problem of the solution is also very simple, Just add the permissions you need, plus the following:

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

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Step Three, start the system database Master

The master database is the first system library in the SQL Server system startup process and is a very critical database. If the library cannot be opened normally, SQL Server will not start properly.

As with other databases, the master database is also divided into data files and log files, the START process is opened in turn, and then do the recovery action, if the process is not a problem, in the errorlog log file, we will see the following sentence:

If there is any problem with this process, the startup process of SQL Server is interrupted and the startup process fails.

And the error of this process is nothing more than concentrating, let's analyze:

<1> the data file or log file for master data is not found in the specified path

The path to the most important system database for this SQL Server, which exists as a registry, in the registry key, you can see

If the system database cannot be found under this path, the service is not able to start, and will generate the corresponding error log information, we can simulate, turn off the service, remove the two files away, and then start to look at:

First, the service is failed to start

Let's take a look at the system log

Look at the log information of errorlog

As you can see, the error message is quite detailed. Let's look at the second case.

The <2> file was found, but no access is available, or the file cannot be opened in an exclusive manner (the default is exclusive lock for file opening)

This situation is also possible, such as some NB anti-virus software can do this, so that your system library is inaccessible, so the same is not bootable, we see this, the hint of the wrong information:

Take a look at Errorlog's error record:

<3> file found, access rights also have, but the file is a problem, that is, the database is corrupted

This problem also often occurs, such as the disk is broken, recovered after the discovery of file problems, not open normally, this problem we look at the error message:

Information in the log

About the Master System library startup process, basically is the above three kinds of errors, about these three kinds of questions, how should we solve?

workaround : First if the error log based on the nature of the problem, if the first two problems are really good to solve, such as the file is not found, the permissions are not correct, and so on, these problems corresponding to solve the problem, the most difficult is the third situation, In this case, the most ideal situation is that the master database is backed up, the backup file can be restored, everything can be normal, of course, through a violent stop service, copy files can also be resolved.

The most worrying is that the library is not backed up, then how to solve it? The solution to this approach is to rebuild the master data with the help of the SQL Server Installer, but the master database rebuilt in this way will cause the previous SQL Server settings to be emptied out.

The emptied information includes: all account information (meaning need to rebuild), all job information in msdb, etc. (also need to rebuild), user database information (must be re-attached attch)

This series of processes, if it is a production library, can be a very large amount of work!

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Fourth step, start the system resource database, and check the data version information

The resource database is the logical database introduced in SQL Server2005, which is not visible under the instance, but has its physical file, the default name of the primary database is: Mssqlsystemresource.mdf, Log name is: Mssqlsystemresource.ldf

If a problem occurs during the database startup process, SQL Server does not start properly.

This system database is very special, he is a read-only database, fully maintained by SQL Server itself, the user can not change, so we just have to ensure that it is the database files and logs intact, do not need to be any tracking and maintenance.

Of course, if you have to look at this database, you can connect via a single-user DAC.

So this database in general will not happen unexpectedly, basically can start normally, but under special circumstances, can not start the following two kinds of cases:

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

In fact, the error message of its report, similar to the above master database, I would like to cut a map, look at:

This is an error message from the errorlog record.

There are also its own error log messages at the Windows level:

The version of the <2> resource database is inconsistent with the version of SQL Server

This could be an artificial change to the resource database, resulting in an inconsistency between the existing resource database files and the database version, which would also lead to the formation of errors

The Windwos platform also logs the error message, looking at the following picture:

Workaround:

The two problem solving methods for the resource pool are very simple. Just find the database with the same version of SQL Server on this server and copy it.

Of course, the best preventive measures are: every time after the installation of SQL Server or after the patch, the timely backup of the two files, put in a safe place, when used to copy the line , Backup is the bounden duty of the database administrator

of course, sometimes in an emergency situation, can not find the same version of the database, theoretically the library is read-only, so there will be no change, we randomly find a machine, install the same version of the database, and then copy it, of course, it must be noted that this is the same version.

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Fifth step, start the system Database model

Model System database is also the SQL Server startup process used in a very critical database, if the library is damaged, SQL Server startup will also fail, about the model data can not be started because the basic and master similar, also two kinds: 1, Database file is not available or inaccessible; 2. The database file can be accessed but is corrupted.

The way to diagnose this problem is the same as in the above two ways, look at the boot process generated by the Errorlog file or the Windows system log, here we do not reproduce the problem.

We only give a solution to this problem:

1, if the library we have done a backup, the most direct and most effective solution is to restore directly, the Restore method here may be different from the normal library, because the SQL Server instance has not started, our recovery process to take the following procedures:

A. Start SQL Server with parameters, execute the following command at the command prompt, and the SQL Server boot will skip the model database recovery step

net start mssqlserver/f/m/t3608

B. Now restore model database, open SSMs, enter directly

RESTORE DATABASE model from DISK ='G:\data\model.bak'Modeldev  "E:\dataDefaultFileManger\MSSQL10." Mssqlserver\mssql\data\model.mdf 'modellog'  E:\dataDefaultFileManger\MSSQL10. Mssqlserver\mssql\data\model.ldf', replace

C. After a successful recovery, direct restart of SQL Server is possible.

2, the SQL Server shut down, and then directly violent way to copy the model data files back, this method is simple and effective, but the operation of the rules

3, there is also a way to use the setup installation files, rebuild the database, the process is slow, slightly complicated, it is not recommended.

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Sixth step, start the network configuration to connect, provide services externally, use the default 1433 port

When the above several important system libraries have been started, the next step is to start checking the network environment, Network Service configuration, external service, generally speaking, in SQL Server using the network boot protocol has three kinds: Shared Memory, Named Pope and TCP/IP, in fact, the most common in our daily use of TCP/IP is the way, and the default is 1433 ports open.

Let's take a look at the verbose log of this section during normal startup:

The shared memory is a connection to SQL Server for local connectivity via LPC (local Procedure Call) technology. It does not take the network layer, so he is the fastest way to connect. Normal startup will display the above normal log.

The Named pipe method starts normally, and the above log is displayed. Can see.

This one of our most commonly used TCP/IP this way, also normal start, and specify the two access methods, Ipv4/ipv6, and then add a 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 not to start properly, so that we can see the following log information

And there are also records in the Windows system log

Workaround :

In fact, the problem here is still very good to solve, just need to find the application that occupies this port, take measures to let it give this port to let out.

Of course, these problems mean that the client has not been able to access the connection through the remote connection of TCP/IP.

At this time, the general administrator can use SQL Server to provide a "dedicated Administrator Connection" (DAC) to connect, this way we will introduce later.

Of course, this network problem typically occurs during SQL Server startup, or the protocol does not load successfully, and SQL Server reports an error message, but generally does not affect the normal startup of SQL Server. The affected may be just the kind of protocol feature that is problematic.

We just need to follow the log, locate the problem, then solve it, restart it.

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Seventh step, start the msdb system database

Regarding the msdb system database, it is arranged in the system library close to the last one, in addition to the user database and temporary library tempdb, when the boot process has been carried out in this step, in fact, our instance has been started, and can be connected.

We know that the main storage information in msdb is the backup information of each library, the history of various jobs, etc., in fact, many of them are from the user database generated by some objective data.

Let's take a look at what happens to a problem with this library:

I removed this library file, then restarted the service, did not report any errors during startup, and was able to start successfully, we use SSMS directly connected to the past, can also connect normally

But when we click to open the data, we can not see any user database, and will produce an error prompt:

It doesn't seem to work, so let's look at the error log:

Although the importance of this library is slightly worse than the importance of a library such as master, our SQL Server is not available even though it can be started.

Workaround :

There are many ways to solve this problem, because our instance of SQL Server has been able to start properly, and we can take:

1, restore the library with backup, refer to the way before the article (recommended)

2, turn off the service, the use of violent copy files to restore, simple and effective, unconventional operation

3, find the same environment, find the same file, directly copied over to use

4, using the installation files for recovery (not recommended)

--------------------------------------------------------------------------domineering split-line----------------------------------------- --------------------------------------

Eighth, start the user database, complete the integrity checks for each library, and empty the system library's tempdb before starting the user database

The problems encountered in this step are endless, various styles, I intend to reorganize an article, specifically enumerated, this article does not introduce.

But one thing to remember: Before this step, SQL Server emptied the tempdb system library, that is, each restart operation, the system will empty tempdb, and then rebuild, this step is generally not an exception, after success, the following log information will appear:

Step Nineth, start rebuilding another database of the system tempdb

Tempdb is a special library that is recreated each time it restarts, and SQL Server creates it as a version of the model database based on the information recorded in the master database. So as long as we ensure that the model database is not a problem, then the hard drive is not a problem, tempdb database files should be no problem.

All configuration information about Temdb This library is stored in the master database, and the content information is stored in the Model system library.

This poses a problem, and sometimes our master library is backed up from under another machine, so it will record the path of this tempdb library on the original machine so that it will get an error when the startup is created.

So we need to execute the following command to change this library path

A. Start SQL Server with parameters

net start mssqlserver/f/  m  /t3608

B. Modifying the data file and log file paths

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. Normal Startup database can

Another situation is that when you create the file, the hard disk space is not available, or the permissions are not enough, we also modify to a correct path according to the above method, and ensure that the permissions are correct.

You can also change the size of the temp file, which is 4M by default, with the following code:

ALTER database tempdb MODIFY file (name=tempdev,size=100MB); goalter database tempdb MODIFY file (NAME=tempdev, size=100MB); go

At this point, if the entire process above is not a problem, a normal SQL Server can start successfully.

Conclusion

The end of this article ... In order to show all the problems as much as possible, I have done a lot of ruthless destruction of local SQL Server, all kinds of devastation, and strive to be able to re-display a variety of application scenarios problem phenomena, and then as far as possible to find the right solution, of course, there are many cases did not show up, follow-up encounter, Will be added, of course, have encountered can not be resolved, you can leave a message, we analyze and solve.

About the user database startup process, this process is a problem easier to take place, God horse question, recovery, not use and so on, I follow the article listed analysis.

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

The SQL Server database startup process you don't know, and the analysis and resolution techniques for various issues that don't start up