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

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 database, 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 this process of error, nothing but focus on the following several situations, let us 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 it is the first two problems are very good solution, such as the file is not found, the permissions are not correct, and so on, these problems corresponding to solve it, the most difficult is the third situation, the most ideal situation is the master database is backed up, By backing up files for recovery you can, everything can be normal, of course, through the 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, it 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 ' 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 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,

Reference: http://www.cnblogs.com/zhijianliutang/p/4085546.html

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