In the daily management of the maintenance of SQL Server, we need to pay attention to a lot of places, and then sometimes do things do not work, now on the basic principles and solutions to common problems, here to share with you.
Let's start by describing how SQL Server works.
As a Windows service, each instance of SQL Server is a sqlserve.exe process. When this process starts, it reads the startup configuration of the SQL Server instance, detects the hardware, configures the memory and CPU, initializes some SQL Server system configurations, starts the database, and connects to the network. The database then starts normally, and then the client can access it normally.
This is the basic start-up process, that in what situation will not start normally, of course, we can solve the basic situation, such as the execution of file corruption, database corruption, these can not immediately solve the problem, later in the introduction.
1.SQL Server Instance service process does not start correctly
When the Sqlservr.exe process starts, it needs to read some key values of the registry and read some files, and then do the corresponding actions according to these configurations.
What are the key values that need to be read, and what are the general functions of the registry keys:
1) hlm\spftware\microsoft\microsoft SQL server\mssql11. Under the Mssqlserver\mssqlserver,
Audit level: Sets whether SQL Server logs user logon information or server property settings in Management Studio.
Login Mode: Sets whether SQL Server receives only Windows account logins or both Windows and SQL Server accounts.
2) Start parameter Hlm\spftware\microsoft\microsoft SQL server\mssql11. Under the Mssqlserver\mssqlserver\parameters,
SQL Server can have many startup parameters, some of which provide analysis problems.
Here are a few fixed parameters:
-D records the physical location of the data files of the master database
-L records the physical location of the master log file
-E Records the location of the SQL Server error log file
3) Network configuration information Hlm\spftware\microsoft\microsoft SQL server\mssql11. Under the Mssqlserver\mssqlserver\supersocketnetlib,
SQL Server listens to which network protocols, as well as the configuration of each network protocol, is also placed in the registration table.
Read the SQL Server startup information from the registry two issues that you may encounter in this step:
1) Start account permissions issue
If the startup account does not have permission to read the registry or call some of the SQL Server's resource files, the SQL Server process may not be able to be created, and errorlog files may not be generated.
The problem that needs to be solved is to give the startup account permission.
2) log file and folder access issues
For SQL Server to start successfully, the log file errorlog must be created correctly and SQL Server cannot start if the log file cannot be created successfully.
Sometimes the path of the log is incorrect, that is, the-e parameter is specified in an incorrect location, or a process locks the errorlog file.
2. System database does not start properly
When the SQL Server service is turned on, the first step is to start the system database and SQL Server will not start properly if the system database does not open properly.
3. Network Configuration failure
To receive connections from clients, SQL Server must have a network protocol configured, typically tcp/ip,named PIPE,LPC, and the client will not be able to connect to SQL Server properly if the protocol is not properly configured.
4. User data does not start properly
The last step in SQL Server startup is to open the user database, and if the database file is corrupted, or if the database was not shut down properly the last time SQL Server was shut down, you may have trouble opening the user database.
5. SQL Server resources cannot remain healthy online in a clustered environment
SQL Server that is installed in a clustered environment is monitored by the Windows Cluster service to see if SQL Server is working correctly. If the Cluster service is not connected to SQL Server, a switchover may occur. Management requires monitoring and analysis, why SQL Server resources cannot remain online, and why the last switchover occurred.
Tip: The errorlog log records all the serious problems that SQL Server encounters during startup or running, so when SQL Server encounters an exception problem, it first checks the log file. This file typically holds 7, in the order of the time from near to far, and restarting the SQL Server service generates a new Errorlog file.
Start the SQL Server service and database
