Questions about SQL (2012) Suddenly unable to connect to the server

4 reasons why SQL Server cannot start: (the problem is analyzed first)

Original address: http://www.cnblogs.com/JiangLe/p/4000497.html

SQL Server cannot start because of the reason to locate, first of all, to know the SQL Server startup process.

The first step:

Read the registry, create a log file, detect the hardware, and initialize the configuration of your system.

Step Two:

Start the system database.

Step Three:

Prepare your network connection.

Fourth Step:

Start the user database.

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------

Method 1, for the first step

Run-regedit Open the registry

SQL Server key-value pair in Windows server 2012 +sql Server

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL server\ ..... Under

1, the current user does not read the registry permissions when SQL Server failed in the first step, so it cannot be started.

Run-EVENTWVR (Event Viewer) to view the log files.

　　　　

Click on each message to see its full contents.

　　　　

There is nothing wrong with this log file can find the answer from the log file, is not too cool.

Then---

2. SQL Server cannot be started if the log file cannot be successfully created.

Method 2, for the second step

Starting the master database is an important phase, and if the master database does not start properly, the database will not start.

1. The file and path of master cannot be found under the specified path.

2, the file is found but no permission,

3, the file is damaged,

Have you ever wondered why I know about these three questions and I will tell you that this is experience. But you have encountered what to do, one way, is to look at the log, to locate the cause of the error.

Workaround

1. Restore the original backup of master.

2, the corresponding permission to take the object.

The cause of the error when starting the Model system database is similar to that of master.

Workaround

1. Restore the original backup of master.

2, the corresponding permission to take the object.

3, in the start of the database by passing back to the model this link

First step, start SQL Server with parameters

net start mssqlserver/f/m/t3608

Second step, restore model database

Restore Database Model from disk = ' E:\Model.bak ';

Error starting tempdb, there may not be enough space on the server to put tempdb

Workaround

First step: Start with parameters

net start mssqlserver/f/m/t3608

Step Two:

ALTER DATABASE tempdb modify file (name =TEMPDEV,SIZE=100MB);

ALTER DATABASE tempdb modify file (name = TEMPLOG,SIZE=200MB);

Method 3, for the third step

Network connection error, generally appears to be the port is used by other programs.

Workaround:

SQL Server uses 1433 port, so we just have to find out which is the face of the program using this port and kill it.

Run-netstat

　　　　

Attention:

When SQL Server starts the master, tempdb is at the same time as preparing the network connection.

Method 4, for the fourth step

Database file has three kinds of 1 main database files MDF, 2 times to file ndf, 3rd log file LDF.

No matter which file is broken, the database will not start normally.

1. Complete restore database .....

2, if the individual ndf is broken, you can use file restore

The first step: ALTER DATABASE Studio modify file (name = Studio2,offline);----Set the bad file offline.

The second step: ALTER DATABASE Studio set online;

Step three: Restore the bad filegroup.

3, log file corruption is a bit bad, slowly listen to

In simple recovery mode, if the last database was closed gracefully (all committed transactions are written to the hard disk, all rolled-back transactions are revoked) even if there is no LDF file

The database can still be started.

If the LDF file is damaged the best way to recover is to restore the database backup, because sometimes the database can be in an inconsistent state, such as a transaction commits, but the data is only

Log file, if the user can accept such a loss, you can choose DBCC CHECKDB method to repair the database

First of all:

1, ALTER DATABASE Studio set emerggency;

2, ALTER DATABASE Studio set Single_user;

3, DBCC CHECKDB (STUDIO,REPAIR_ALLOW_DATA_LOSS);

A detailed workaround for this problem: (that is,the repair method of the Master table corruption in SQL Server )

original address : http://www.cnblogs.com/ popo0027/archive/2012/08/08/2628044.html

MS SQL Server R2 installed on the local computer may cause the SQL Server service to fail to start due to hard disk causes, and you will see several errors and information in Event Viewer with two hints that may be corrupted by master:
1, Error: Passed to database ' The log scan number (184:416:1) of the log scan operation in master ' is not valid. This error may indicate data corruption, or the log file (. ldf) does not match the data file (. mdf). If this error occurs during replication, recreate the publication. Otherwise, if the problem causes an error during startup, restore from a backup.
2, Information: The master database could not be recovered. SQL Server cannot run. Restore the master database with a full backup, repair it, or regenerate it. For more information about how to regenerate the master database, see SQL Server Books Online.

Reinstalling SQL Server issues is not difficult to resolve, but is too time-consuming. Google to a quick recovery method, share the following:

1, copy model.mdf, Mastlog.ldf, Model.mdf, Modellog.ldf, Msdbdata.mdf, msdblog.ldf files.
from
X:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\binn\templates
to
X:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data
Note: The above "X:\Program Files\Microsoft SQL Server" is the installation directory for SQL Server. The following "C:\Program Files\Microsoft SQL Server" is the directory under the system disk

2, locate and execute the installation command

First locate the installation command:
C:\Program files\ Microsoft SQL Server\110\setup Bootstrap\sqlserver2012\setup.exe

Note: If sql2008 is C:\Program Files\Microsoft SQL Server\100\setup Bootstrap\release\setup.exe

If you are just Windows Authentication mode, you only need the following syntax:
Setup/action=rebuilddatabase/quiet/instancename=<instance name>/sqlsysadminaccounts=<accounts>
In the case of composite authentication mode, you need to provide the password for the SA using the/SAPWD parameter:
Setup/action=rebuilddatabase/quiet/instancename=<instance name>/sqlsysadminaccounts=<accounts>/ Sapwd=<sa password>

I set up the composite authentication mode, the SQL Server system administrator account is the Administrators group, the SA password is 123456. And just one default instance: MSSQLSERVER.
So execute the following command at the command line:
setup/action=rebuilddatabase/quiet/instancename=mssqlserver/sqlsysadminaccounts=administrators/sapwd=123456

There is no prompt message (success or failure) after execution, but you can view the installation log in C:\Program Files\Microsoft SQL Server\100\setup Bootstrap\log\summary.txt immediately.

Finally, the SQL Server service starts successfully in SQL Server Configuration Manager.

Questions about SQL (2012) Suddenly unable to connect to the server