Содержание
- Handling Errors and Messages
- Степени серьезности ошибок компонента Database Engine
- Уровни серьезности
- Серьезность пользовательских сообщений об ошибках
- Серьезность ошибки и конструкция TRY…CATCH
- определение серьезности ошибки
- A network-related or instance-specific error occurred while establishing a connection to SQL Server
- Error messages
- «A network-related or instance-specific error occurred while establishing a connection to SQL Server. Verify that the instance name is correct and that SQL Server is configured to allow remote connections»
- «SQL Server does not exist or access denied»
- Gather information for troubleshooting the error
- Option 1: Use the SQL Check tool to gather the required information
- Option 2: Collect the data individually using the following procedures
- Get the instance name from Configuration Manager
- Get the IP address of the server
- Get the TCP port of the instance
- Step 1пјљVerify that the instance is running
- Option 1: Using the output file from the SQLCheck tool
- Option 2: Use SQL Server Configuration Manager
- Option 3: Use PowerShell commands
- Step 2: Verify that the SQL Server Browser service is running
- Option 1: Using the output file from SQLCheck tool
- Option 2: Use SQL Server Configuration Manager
- Step 3: Verify the server name in the connection string
- Step 4: Verify the aliases on the client machines
- Option 1: Using the output file from the SQLCheck tool
- Option 2: Check aliases in SQL Server Configuration Manager
- Option 3: Check aliases in SQL Server Client Network Utility
- Step 5: Verify the firewall configuration
- Default instance of SQL Server
- Named instance of SQL Server
- Step 6: Verify the enabled protocols on SQL Server
- Option 1: Using the output file from SQLCheck tool
- Option 2: Use SQL Server Configuration Manager
- Step 7: Test TCP/IP connectivity
- Step 8: Test local connection
- Step 9: Test remote connection
Handling Errors and Messages
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
When an application calls an ODBC function, the driver executes the function and returns diagnostic information in two ways: A return code indicates the overall success or failure of an ODBC function, and diagnostic records provide detailed information about the function. Diagnostic records include a header record and status records. At least one diagnostic record, the header record, is returned even if the function succeeds.
Diagnostic information is used at development time to catch programming errors, such as invalid handles and syntax errors in hard-coded SQL statements. It is also used at run time to catch run-time errors and warnings, such as data truncation, rule violations, and syntax errors in SQL statements entered by the user. Program logic is generally based on return codes.
For example, after an application calls SQLFetch to retrieve the rows in a result set, the return code indicates whether the end of the result set was reached (SQL_NO_DATA), if any informational messages were returned (SQL_SUCCESS_WITH_INFO), or if an error occurred (SQL_ERROR).
If the SQL Server Native Client ODBC driver returns anything other than SQL_SUCCESS, the application can call SQLGetDiagRec to retrieve any informational or error messages. Use SQLGetDiagRec to scroll up and down the message set if there is more than one message.
The return code SQL_INVALID_HANDLE always indicates a programming error and should never be encountered at run time. All other return codes provide run-time information, although SQL_ERROR may indicate a programming error.
The original Microsoft SQL Server native API, DB-Library for C, allows an application to install callback error-handling and message-handling functions that return errors or messages. Some Transact-SQL statements, such as PRINT, RAISERROR, DBCC, and SET, return their results to the DB-Library message handler function instead of to a result set. However, the ODBC API has no such callback capability. When the SQL Server Native Client ODBC driver detects messages coming back from SQL Server, it sets the ODBC return code to SQL_SUCCESS_WITH_INFO or SQL_ERROR and returns the message as one or more diagnostic records. Therefore, an ODBC application must carefully test for these return codes and call SQLGetDiagRec to retrieve message data.
For information about tracing errors, see Data Access Tracing. For information about enhancements to error tracing added in SQL Server 2012 (11.x), see Accessing Diagnostic Information in the Extended Events Log.
Источник
Степени серьезности ошибок компонента Database Engine
Применимо к: SQL Server (все поддерживаемые версии)
При возникновении ошибки ядром СУБД SQL Server серьезность ошибки указывает тип проблемы, возникшей в SQL Server.
Уровни серьезности
В следующей таблице перечислены уровни серьезности ошибок, вызванных ядром СУБД SQL Server.
Степень серьезности | Описание |
---|---|
0-9 | Информационные сообщения, возвращающие сведения о состоянии или оповещающие о несерьезных ошибках. Ядро СУБД не вызывает системные ошибки с серьезностью от 0 до 9. |
10 | Информационные сообщения, возвращающие сведения о состоянии или оповещающие о несерьезных ошибках. По соображениям совместимости ядро СУБД преобразует уровень серьезности 10 в серьезность 0 перед возвратом сведений об ошибке вызывающем приложению. |
11-16 | Ошибки, которые могут исправляться пользователем. |
11 | Данный объект или сущность не существует. |
12 | Специальный уровень серьезности для запросов, не использующих блокировку из-за специальных указаний запросов. В некоторых случаях операции чтения, выполняемые этими инструкциями, могут давать в результате несогласованные данные, так как блокировки не приспособлены для обеспечения согласованности. |
13 | Указывает ошибки взаимоблокировки транзакций. |
14 | Указывает ошибки, связанные с безопасностью, например запрет на разрешение. |
15 | Указывает синтаксические ошибки в команде Transact-SQL. |
16 | Обозначает общие ошибки, которые могут исправляться пользователем. |
17-19 | Обозначаются программные ошибки, которые не могут исправляться пользователем. Сообщите администратору системы о данной проблеме. |
17 | Указывает, что инструкция привела SQL Server к нехватке ресурсов (например, памяти, блокировок или дискового пространства для базы данных) или превышению некоторого ограничения, установленного системным администратором. |
18 | Указывает на проблему в программном обеспечении ядра СУБД, но инструкция завершает выполнение и поддерживается подключение к экземпляру ядра СУБД. Необходимо сообщить системному администратору о каждом случае возникновения ошибки со степенью серьезности 18. |
19 | Указывает, что превышено ненастранимое ограничение ядра СУБД и завершен текущий пакетный процесс. Сообщения об ошибке со степенью серьезности 19 и выше останавливают выполнение текущего пакета. Ошибки со степенью серьезности 19 происходят редко и должны устраняться системным администратором или основной службой технической поддержки. При возникновении ошибок со степенью серьезности 19 обратитесь к системному администратору. Сообщения об ошибках со степенью серьезности от 19 до 25 записываются в журнал ошибок. |
20–24 | Укажите системные проблемы и являются неустранимыми ошибками, что означает, что задача ядра СУБД, выполняющая инструкцию или пакет, больше не выполняется. Задача записывает сведения о том, что произошло, и затем прекращает работу. В большинстве случаев подключение приложения к экземпляру ядра СУБД также может завершиться. В этом случае приложение, возможно, не сможет вновь выполнить подключение (в зависимости от проблемы).
Сообщения об ошибках этого диапазона могут влиять на все процессы, обращающиеся к данным в одной и той же базе данных, и могут указывать на то, что база данных или объект повреждены. Сообщения об ошибках со степенью серьезности от 19 до 24 записываются в журнал ошибок. |
20 | Обозначает, что при выполнении инструкции возникла проблема. Так как проблема повлияла только на текущую задачу, маловероятно, что повреждена база данных. |
21 | Обозначает, что возникла проблема, влияющая на все задачи в текущей базе данных, но маловероятно, что повреждена база данных. |
22 | Обозначает, что таблица или индекс, указанные в сообщении, повреждены из-за программной проблемы или проблемы оборудования.
Ошибки степени серьезности 22 происходят редко. При возникновении такой ошибки запустите инструкцию DBCC CHECKDB, чтобы определить, не повреждены ли другие объекты в базе данных. Проблема может быть ограничена только буферным кэшем и не затрагивать сам диск. В этом случае перезапуск экземпляра ядра СУБД исправляет проблему. Чтобы продолжить работу, необходимо повторно подключиться к экземпляру ядра СУБД; в противном случае используйте DBCC для устранения проблемы. В некоторых случаях может потребоваться восстановление базы данных. Если перезапуск экземпляра ядра СУБД не устранит проблему, проблема находится на диске. Иногда удаление объекта, указанного в сообщении об ошибке, может решить проблему. Например, если сообщение сообщает, что экземпляр ядра СУБД обнаружил строку длиной 0 в некластеризованном индексе, удалите индекс и перестройте его. |
23 | Обозначает, что из-за проблем в оборудовании или программном обеспечении целостность всей базы данных находится под вопросом.
Ошибки степени серьезности 23 происходят редко. При возникновении такой ошибки запустите инструкцию DBCC CHECKDB, чтобы определить экстент повреждения. Проблема может быть ограничена только кэшем, и не затрагивать сам диск. В этом случае перезапуск экземпляра ядра СУБД исправляет проблему. Чтобы продолжить работу, необходимо повторно подключиться к экземпляру ядра СУБД; в противном случае используйте DBCC для устранения проблемы. В некоторых случаях может потребоваться восстановление базы данных. |
24 | Обозначает неисправность носителя. Возможно, системный администратор должен восстановить базу данных. Кроме того, может потребоваться обращение к поставщику оборудования. |
Серьезность пользовательских сообщений об ошибках
Процедураsp_addmessage может использоваться для добавления пользовательских сообщений об ошибках с уровнем серьезности от 1 до 25 в представление каталога sys.messages . Эти пользовательские сообщения об ошибках могут использоваться инструкцией RAISERROR. Дополнительные сведения см. в разделе sp_addmessage (Transact-SQL).
Инструкция RAISERROR может применяться для формирования пользовательских сообщений об ошибках с уровнем серьезности от 1 до 25. Инструкция RAISERROR может либо ссылаться на определенное пользователем сообщение, находящееся в представлении каталога sys.messages , либо динамически создавать сообщение. Если при формировании ошибки используется пользовательское сообщение об ошибках, хранимое в представлении sys.messages, то уровень серьезности, указанный в инструкции RAISERROR, переопределяет уровень серьезности, указанный в представлении sys.messages. Дополнительные сведения см. в разделе справки RAISERROR (Transact-SQL).
Серьезность ошибки и конструкция TRY…CATCH
Конструкция TRY…CATCH перехватывает все ошибки исполнения с уровнем серьезности выше 10, которые не прерывают подключение к базе данных.
Ошибки с уровнем серьезности от 0 до 10 являются информационными сообщениями и не приводят к выходу процесса выполнения из блока CATCH конструкции TRY…CATCH.
Ошибки, приводящие к прерыванию подключения к базе данных и обычно имеющие уровень серьезности от 20 до 25, не обрабатываются блоком CATCH, так как при разрыве соединения выполнение прерывается.
Дополнительные сведения см. в разделе TRY. CATCH (Transact-SQL).
определение серьезности ошибки
Чтобы определить серьезность ошибки, инициирующей выполнение блока CATCH конструкции TRY…CATCH, можно использовать системную функцию ERROR_SEVERITY. Если вызов происходит не из блока CATCH, функция ERROR_SEVERITY возвращает значение NULL. Дополнительные сведения см. в разделе ERROR_SEVERITY (Transact-SQL).
Источник
Applies to: В SQL Server
When connecting to a SQL Server instance, you may encounter one or more of the error messages below. This article provides some steps to help you troubleshoot these errors, which are provided in order of the issues from simple to complex.
Error messages
The complete error messages vary depending on the client library that is used in the application and the server environment. You can check the following details to see if you’re encountering one of the following error messages:
provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server (Microsoft SQL Server, Error: 53) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server (Microsoft SQL Server, Error: 53)
provider: TCP Provider, error: 0 — No such host is known. (Microsoft SQL Server, Error: 11001)
provider: SQL Network Interfaces, error: 26 — Error Locating Server/Instance Specified A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
provider: SQL Network Interfaces, error: 26 — Error Locating Server/Instance Specified
Login timeout expired SQL Server Native Client Data Link Error
[Microsoft SQL Server Native Client 10.0]: Login timeout expired
[Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[Microsoft SQL Server Native Client 10.0]: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
provider: TCP Provider, error: 0
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
Microsoft SQL Server, Error: 10060
provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server
Microsoft SQL Server, Error: 53
The network path was not found
[Microsoft][SQL Server Native Client 11.0]TCP Provider: No connection could be made because the target machine actively refused it SQL Server Native Client Data Link Error
[Microsoft][SQL Server Native Client 11.0]TCP Provider: No connection could be made because the target machine actively refused it.
[Microsoft][SQL Server Native Client 11.0]Login timeout expired.
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
«SQL Server does not exist or access denied»
This error usually means that the client can’t find the SQL Server instance. This issue occurs when at least one of the following problems exists:
- The name of the computer hosting SQL Server is incorrect.
- The instance doesn’t resolve the correct IP.
- The TCP port number isn’t specified correctly.
For troubleshooting connectivity issues in high availability scenarios, see the following articles:
Gather information for troubleshooting the error
We recommend that you gather the information listed in this section using one of the options below before proceeding with the actual steps to troubleshoot the error.
Option 1: Use the SQL Check tool to gather the required information
If you can sign in locally to the SQL Server computer and have administrator access, use SQLCheck from the Microsoft SQL Networking GitHub repository. This tool provides most of the information required for troubleshooting in one file. Review the tool’s home page for more information on using the tool and the information it gathers. You can also check the recommended prerequisites and checklist page.
Option 2: Collect the data individually using the following procedures
Get the instance name from Configuration Manager
On the server that hosts the SQL Server instance, use SQL Server Configuration Manager to verify the instance name:
Configuration Manager is automatically installed on the computer when SQL Server is installed. Instructions on starting Configuration Manager vary slightly by versions of SQL Server and Windows. For version-specific details, see SQL Server Configuration Manager.
Sign in to the computer hosting the instance of SQL Server.
Start SQL Server Configuration Manager.
In the left pane, select SQL Server Services.
In the right pane, verify the name of the instance of the database engine.
- SQL SERVER (MSSQLSERVER) indicates a default instance of SQL Server. The name of the default instance is .
- SQL SERVER ( ) indicates a named instance of SQL Server. The name of the named instance is .
Get the IP address of the server
You can use the following steps to get the IP address of the computer hosting the instance of SQL Server.
On the Start menu, select Run. In the Run window, type cmd, and then select OK.
In the Command Prompt window, type ipconfig/all and then press Enter. Note down the IPv4 address and the IPv6 address.
SQL Server can connect by using either IP version 4 protocol or IP version 6 protocol. Your network could allow either or both.
Get the TCP port of the instance
In most cases, you connect to the Database Engine on another computer by using the TCP protocol. To get the TCP port of the instance, follow these steps:
Use SQL Server Management Studio on the computer running SQL Server and connect to the instance of SQL Server. In Object Explorer, expand Management, expand SQL Server Logs, and then double-click the current log.
In the Log File Viewer, select Filter on the toolbar. In the Message contains text box, type server is listening on, select Apply filter, and then select OK.
A message like Server is listening on [ ‘any’ 1433] should be listed.
This message indicates that the instance of SQL Server is listening on all IP addresses on this computer (for IP version 4) and TCP port 1433. (TCP port 1433 is usually the port that’s used by the Database Engine or the default instance of SQL Server. Only one instance of SQL Server can use this port. If more than one instance of SQL Server is installed, some instances must use other port numbers.) Note down the port number used by the SQL Server instance that you’re trying to connect to.
- IP address 127.0.0.1 is probably listed. It’s called the loopback adapter address. Only processes on the same computer can use the IP address to connect.
- You can also view the SQL Server error log by using a text editor. By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files. For more information, see Viewing the SQL Server error log.
Step 1пјљVerify that the instance is running
Option 1: Using the output file from the SQLCheck tool
Search the output from SQLCheck file for «SQL Server Information».
In the section titled «Services of Interest», find your SQL Server instance under Name and Instance (for named instances) columns and check its status by using Started column. If the value is True, the services are started. Otherwise the service is currently not running.
If the service isn’t running, start the service by using either SQL Server management studio, SQL Server Configuration manager, PowerShell, or Services applet.
Option 2: Use SQL Server Configuration Manager
To verify that the instance is running, select SQL Server Services in SQL Server Configuration Manager and check the symbol by the SQL Server instance.
- A green arrow indicates that an instance is running.
- A red square indicates that an instance is stopped.
If the instance is stopped, right-click the instance and select Start. Then, the server instance starts, and the indicator becomes a green arrow.
Option 3: Use PowerShell commands
You can use the following command in PowerShell to check the status of SQL Server services on the system:
You can use the following command to search the error log file for the specific string «SQL Server is now ready for client connections. This is an informational message; no user action is required.»:
Step 2: Verify that the SQL Server Browser service is running
This step is required only for troubleshooting connectivity issues with named instances.
Option 1: Using the output file from SQLCheck tool
Search the output from SQLCheck file for «SQL Server Information».
In the section titled «Services of Interest», search for SQLBrowser in the Name column and check its status using the Started column. If the value is True, the service is started. Otherwise, the service is currently not running, and you need to start it. For more information, see Start, stop, pause, resume, restart SQL Server services.
Option 2: Use SQL Server Configuration Manager
To connect to a named instance, the SQL Server Browser service must be running. In SQL Server Configuration Manager, locate the SQL Server Browser service and verify that it’s running. If it’s not running, start the service. The SQL Server Browser service isn’t required for default instances.
For more information on using SQL Server Browser service in your environment, see SQL Server Browser service.
For more information on stopping and starting SQL Services, see Start, stop, pause, resume, restart SQL Server services.
If you can’t have the SQL Server Browser service running in your environment, see Connecting to SQL server named instance without SQL Server browser service.
Step 3: Verify the server name in the connection string
You often encounter errors when an incorrect server name is specified in the connection string. Make sure that the server name matches the one that you retrieved in the previous steps.
If you are using the SQLCheck tool, review the NetBios Name/FQDN values in the Computer Information section of the output file.
- For examples on connection strings, see SQL Server Connection Strings.
- For more detailed examples, see Proof of concept connecting to SQL using ADO.NET under Homepage for SQL client programming.
Step 4: Verify the aliases on the client machines
Aliases are often used in client environments when you connect to SQL Server with an alternate name or when there are name resolution issues in the network. They’re created by using SQL Server Configuration Manager or client network utility. An incorrect alias can cause the connections from your applications to connect to the wrong server, resulting in failure. Use the following methods to check for incorrect aliases. You can also use a tool (such as SQLCHECK) on the client machine to check for aliases and various other connectivity-related settings on a client machine.
The following options only apply to the applications that use SQL Server Native Client to connect to SQL Server.
Option 1: Using the output file from the SQLCheck tool
In the SQLCheck output file, search for the string SQL Aliases. (This string will be inside the Client Security and Driver Information section of the file)
Review the entries in the table. If there’s none present, there are no aliases on the computer. If there’s an entry, review the information to ensure the server name and port number are set to the correct values.
Example output:
SQL Aliases:
The above indicates that prodsql is an alias for a SQL Server called prod_sqlserver that is running on port 1430.
Option 2: Check aliases in SQL Server Configuration Manager
- In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, and select Aliases.
- Check whether any aliases are defined for the server that you’re trying to connect to. If the aliases exist, follow these steps:
- Open the Properties pane of the alias.
- Rename the value in the Alias Name field (for example, if your server name is MySQL, rename it as MySQL_test) and retry the connection. If the connection works, your alias is incorrect and may come from an old configuration that is no longer needed. If the connection doesn’t work, rename the alias back to its original name and go to the next step.
- Check the connection parameters for the alias and make sure that they’re correct. The following common scenarios can cause connectivity problems:
Incorrect IP address for the Server field. Make sure that the IP address matches the entry in the SQL Server error log file.
Incorrect server name in the Server field. For example, your server alias points to the correct server name. However, the connections will fail if the value of the server name parameter is incorrect.
Incorrect pipe name format (assuming that you use a named pipes alias).
- When connecting to a default instance named Mydefaultinstance, the pipe name should be \Mydefaultinstancepipesqlquery.
- When connecting to a named instance MySQLNamed, the pipe name should be \MySQLpipeMSSQL$Namedsqlquery.
Option 3: Check aliases in SQL Server Client Network Utility
- Open SQL Server Client Network Utility by typing cliconfg.exe in your Run command.
- Follow step 2 in Option 2: Check aliases in SQL Server Configuration Manager.
Step 5: Verify the firewall configuration
You can verify the firewall configuration depending on the default instance or named instance.
If you are using third party firewalls in your network, the concepts still apply. However, you may have to work with your network administrator or consult the firewall product’s documentation for more information on configuring the firewall to allow necessary ports for communication with SQL Server.
Default instance of SQL Server
A default instance typically runs on port 1433. Some installations also use a non-standard port (other than 1433) to run SQL instances. The firewall may block either port. To check the port number further, follow these steps:
- Determine the port your SQL instance is running on, see Get the TCP port of the instance.
- If it does work, it indicates that the firewall is allowing communication through that port. You need to change your connection string in order to use the port number and your server name in the connection string of your application.
If your SQL Server is configured to listen on port 1433, make sure that firewalls on the network between the client and the server allow traffic on that port. Review Configure a Windows Firewall for Database Engine Access and work with your network administrator to implement necessary solutions.
If your SQL Server default instance isn’t using 1433, try to append the port number of SQL Server to the server name by using the format ,
and see whether it works. For example, your SQL instance name is MySQLDefaultinstance and it’s running on port 2000. Specify the server name as MySQLServer, 2000 and see whether it works.
If it doesn’t work, it indicates the firewall is blocking the port. You can follow the instructions at Configure a Windows Firewall for Database Engine Access or work with your network administrator to add the port to the firewall exclusion list.
Named instance of SQL Server
If your SQL instance is a named instance, it may be configured to use either dynamic ports or a static port. In either case, the underlying network libraries query the SQL Server Browser service running on your SQL Server machine through UDP port 1434 to enumerate the port number for the named instance. If a firewall between the client and the server blocks this UDP port, the client library can’t determine the port (a requirement for connection) and the connection fails. To check the connection, you can use one of the following methods:
Method 1: Check connection by specifying the port number in your connection string.
Determine the port your SQL instance is running on, see Get the TCP port of the instance.
Try to connect to the named instance by using the port number appended to the server name in the format ,
and see if that works. For example, if your SQL instance name is MySQLNamedinstance and it’s running on port 3000, specify the server name as MySQLNamedinstance,3000.
If it does work, it indicates the firewall is blocking the UDP port 1434 or the instance is hidden from SQL Server Browser.
If it doesn’t work, it indicates one of the following situations:
Either UDP port 1434 is blocked or the static port is blocked, or both. To confirm whether it’s the UDP port or the static port, use Portqry.
The instance is hidden from the SQL Server Browser service.
Method 2: Check the connection by using the PortQryUI tool.
Use the PortQryUI tool with your named instance and observe the resulting output. You may see a message that the UDP port 1434 is filtered. This message indicates that the port is blocked on the network. For instructions on how to use the tool, see Using the PortQryUI Tool with SQL Server.
Determine whether the SQL Server instance is listening on dynamic or static ports. Then use the following method that is relevant to your scenario. If you aren’t sure, see How to check if SQL Server is listening on a dynamic port or static port.
Scenario 1: Dynamic ports. In this case, ensure that the SQL Server Browser service is started and UDP port 1434 isn’t blocked on the firewall between the client and the server. If you can’t do either of these things, you should switch your SQL Server instance to a static port and use the procedure documented in Configure a Server to Listen on a Specific TCP Port.
Scenario 2: Static port configuration. Either SQL Server Browser isn’t running or UDP 1434 can’t be opened on the firewall. In this case, make sure to specify the static port in your connection string and that the firewall doesn’t block the port. For more information, review Configure a Windows Firewall for Database Engine Access.
Step 6: Verify the enabled protocols on SQL Server
In some installations of SQL Server, connections to the Database Engine from another computer aren’t enabled unless an administrator manually enables them. You can use one of the following options to check and enable the necessary protocols to allow remote connections to SQL Server Database Engine.
Option 1: Using the output file from SQLCheck tool
Search the SQLCheck output file for «Details for SQL Server instance» section and locate the information section for your SQL Server instance.
In the section, find the values listed in the following table to determine if the SQL Server protocols are enabled:
Value name | Implication | More information |
---|---|---|
Shared Memory Enabled | Can either be true of false — only affects local connections. | Creating a Valid Connection String Using Shared Memory Protocol |
Named Pipes Enabled | If false, both local and remote connections using Named pipes will fail | Choosing a Network Protocol |
TCP Enabled | If false, both local and remote connections using TCP/IP will fail. Note The majority of the SQL Server installations use TCP/IP as the communication protocol between server and the client. |
Choosing a Network Protocol |
Enable required protocols by using SQL Server Configuration Manager or SQL Server PowerShell. For more information, see Enable or Disable a Server Network Protocol.
After enabling a protocol, the Database Engine must be stopped and restarted for the change to take effect.
Option 2: Use SQL Server Configuration Manager
To enable connections from another computer by using the SQL Server Configuration Manager, follow these steps:
Open the SQL Server Configuration Manager.
In the left pane, expand SQL Server Network Configuration, and then select the instance of SQL Server that you want to connect to. The right pane lists the connection protocols available. Shared Memory is normally enabled. It can only be used from the same computer, so most installations leave Shared Memory enabled. To connect to SQL Server from another computer, use TCP/IP. If TCP/IP isn’t enabled, right-click TCP/IP, and then select Enable.
If you change the enabled setting for any protocol, restart the Database Engine. In the left pane, select SQL Server Services. In the right-pane, right-click the instance of the Database Engine, and then select Restart.
Step 7: Test TCP/IP connectivity
Connecting to SQL Server by using TCP/IP requires that Windows establish the connection. You can use the following steps to test TCP connectivity by using the ping tool.
On the Start menu, select Run. In the Run window, type cmd and select OK.
In the Command Prompt window, type ping and the IP address of the computer that’s running SQL Server. For example:
- IPv4: ping 192.168.1.101
- IPv6: ping fe80::d51d:5ab5:6f09:8f48%11
If your network is configured properly, ping returns Reply from followed by some additional information. If ping returns Destination host unreachable or Request timed out , TCP/IP isn’t correctly configured. Errors at this point indicate a problem with the client computer, the server computer, or something about the network such as a router. To troubleshoot network problems, see Advanced troubleshooting for TCP/IP issues.
If the ping test succeeds by using the IP address, test whether the computer name can be resolved to the TCP/IP address. On the client computer, in the Command Prompt window, type ping and the name of the computer that’s running SQL Server. For example, ping newofficepc .
If ping to the IP address succeeds, but ping to the computer name returns Destination host unreachable or Request timed out , you might have old (stale) name resolution information cached on the client computer. Type ipconfig /flushdns to clear the DNS (Dynamic Name Resolution) cache. Then ping the computer by name again. When the DNS cache is empty, the client computer checks the latest information about the IP address for the server computer.
If your network is configured properly, ping returns Reply from followed by some additional information. If you can successfully ping the server computer by IP address but receive an error such as Destination host unreachable or Request timed out when pinging by computer name, then name resolution isn’t correctly configured. For more information, see how to Troubleshoot Basic TCP/IP Problems. Successful name resolution isn’t required to connect to SQL Server. However, if the computer name can’t be resolved to an IP address, connections must be made to specify the IP address. Name resolution can be fixed later.
You can also use either Test-NetConnection or Test-Connection cmdlet to test TCP connectivity according to the PowerShell version that’s installed on the computer. For more information on PowerShell cmdlet, see Cmdlet Overview.
Step 8: Test local connection
Before troubleshooting a connection problem from another computer, test your ability to connect from a client application installed locally on the computer that is running SQL Server. Local connection avoids issues with networks and firewalls.
This procedure requires SQL Server Management Studio. If you don’t have Management Studio installed, see Download SQL Server Management Studio (SSMS).
If you can’t install Management Studio, you can test the connection by using the sqlcmd.exe utility. sqlcmd.exe is installed with the Database Engine. For information about sqlcmd.exe, see sqlcmd Utility.
Sign in to the computer where SQL Server is installed by using a login that can access SQL Server. During installation, SQL Server requires at least one login to be specified as a SQL Server administrator. If you don’t know an administrator, see Connect to SQL Server When System Administrators Are Locked Out.
On the Start page, type SQL Server Management Studio, or on the Start menu of the older versions of Windows, select All Programs, select Microsoft SQL Server, and then select SQL Server Management Studio.
On the Connect drop-down menu, select Database Engine. In the Authentication box, select Windows Authentication. In the Server name box, type one of the following connection types:
Connecting to | Type | Example |
---|---|---|
Default instance | ACCNT27 | |
Named Instance | ACCNT27PAYROLL |
When connecting to SQL Server from a client application on the same computer, the shared memory protocol is used. Shared memory is a type of local named pipe, so you sometimes encounter errors related to pipes.
If you receive an error at this point, you must resolve it before proceeding. Your login might not be authorized to connect. Your default database might be missing.
You can’t troubleshoot the problem without enough information because some error messages are passed to the client intentionally. This is a security feature to avoid providing an attacker with information about SQL Server. To view the details about the error, see the SQL Server error log.
If you receive error 18456 Login failed for user, Books Online article MSSQLSERVER_18456 contains additional information about error codes. Aaron Bertrand’s blog also has an extensive list of error codes at Troubleshooting Error 18456 (external link). You can view the error log by using SSMS (if you can connect), in the Management section of the Object Explorer. Otherwise, you can view the error log with the Windows Notepad program. The default location varies with your version and can be changed during setup. The default location for SQL Server 2019 (15.x) is C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLLogERRORLOG.
If you can connect by using shared memory, test connecting by using TCP. You can force a TCP connection by specifying tcp: before the name. Here are the examples:
Connecting to: | Type: | Example: |
---|---|---|
Default instance | tcp: | tcp:ACCNT27 |
Named Instance | tcp: | tcp:ACCNT27PAYROLL |
If you can connect by using shared memory but not TCP, you must fix the TCP problem. The most likely issue is that TCP isn’t enabled. To enable TCP, see Step 6: Verify the enabled protocols on SQL Server.
If your goal is to connect by using an account other than an administrator account, you can begin by connecting as an administrator. Then, try to connect again with the Windows Authentication login or the SQL Server Authentication login that the client application uses.
Step 9: Test remote connection
Once you can connect by using TCP on the same computer, it’s time to try to connect from the client computer. You could use any client application, but to avoid complexity, install the SQL Server Management tools on the client. After installation, try to use SQL Server Management Studio.
Use SQL Server Management Studio on the client computer and try to connect by using the IP address and the TCP port number in the format IP address comma port number. For example, 192.168.1.101,1433 . If this connection fails, you probably have one of the following problems:
ping of the IP address doesn’t work. This indicates a general TCP configuration problem. Go back to the section Step 7: Test TCP/IP connectivity.
SQL Server isn’t listening on the TCP protocol. Go back to the section Step 6: Verify the enabled protocols on SQL Server.
SQL Server is listening on a port other than the port that you specified. Go back to the section Get the TCP port.
The SQL Server TCP port is being blocked by the firewall. Go back to the section step 5: Verify the firewall configuration.
Once you can connect by using the IP address and port number, review the following scenarios:
If you connect to a default instance that is listening on any port other than 1433, you must use either the port number in the connection string or create an alias on the client machine to connect to the default instance. The SQL Server Browser service can’t enumerate ports of the default instance.
If you connect to a named instance, try to connect to the instance in the format IP address backslash instance name. (For example, 192.168.1.101 .) If this action doesn’t work, it means that the port number isn’t being returned to the client. The problem is related to the SQL Server Browser service, which provides the port number of a named instance to the client. Here are the solutions:
- Start the SQL Server Browser service. See the instructions to start browser in SQL Server Configuration Manager.
- The SQL Server Browser service is being blocked by the firewall. Open UDP port 1434 in the firewall. Go back to the section Step 5: Verify the firewall configuration. Make sure that you’re opening a UDP port, not a TCP port.
- The UDP port 1434 information is being blocked by a router. UDP communication (user datagram protocol) isn’t designed to pass through routers and keeps the network from getting filled with low-priority traffic. You can configure your router to forward UDP traffic, or you can provide the port number every time you connect.
- If the client computer is using Windows 7, Windows Server 2008, or a more recent operating system, the client operating system might drop the UDP traffic because the response from the server is returned from a different IP address that was queried. This action is a security feature blocking «loose source mapping.» For more information, see the Multiple Server IP Addresses section of the Books Online article Troubleshooting: Timeout Expired. (This article is from SQL Server 2008 R2, but the principals still apply. You can configure the client to use the correct IP address or provide the port number every time you connect.)
Once you can connect by using the IP address (or IP address and instance name for a named instance), try to connect by using the computer name (or computer name and instance name for a named instance). Put tcp: in front of the computer name to force a TCP/IP connection. For example, for the default instance on a computer named ACCNT27, use tcp:ACCNT27 . For a named instance called PAYROLL, on that computer use tcp:ACCNT27PAYROLL . If you can connect by using the IP address but not by using the computer name, you have a name resolution problem. Go back to the section Step 7: Test TCP/IP connectivity.
Once you can connect by using the computer name forcing TCP, try to connect by using the computer name without forcing TCP. For example, for a default instance, and just use a computer name such as CCNT27. For a named instance, use the computer name and instance name like ACCNT27PAYROLL. If you can connect while forcing TCP, but not without forcing TCP, the client is probably using another protocol such as named pipes. To fix this issue, follow the steps:
- On the client computer, use SQL Server Configuration Manager. In the left-pane, expand SQL Native Client Configuration, and then select Client Protocols.
- On the right-pane, make sure that TCP/IP is enabled. If TCP/IP is disabled, right-click TCP/IP and select Enable.
- Make sure that the protocol order for TCP/IP is a smaller number than the named pipes (or VIA on older versions) protocols. Generally, you should leave shared memory as order 1 and TCP/IP as order 2. Shared memory is only used when the client and SQL Server are running on the same computer. All enabled protocols are tried in order until one succeeds, but shared memory is skipped when the connection isn’t on the same computer.
Источник
Microsoft SQL Server Ошибки
Useful links
- System Error Messages
- Database Engine Error Severities
- Integration Services Error and Message Reference
- View and Read SQL Server Setup Log Files
- Troubleshoot the SQL Server Utility
- Common Issues: Licensing Errors
- SQL Server 2016 Distributed Replay Errors
- The Instance ID MSSQLSERVER Is Already In Use
- SQL Server: Detach/Attach Gotchas!
SQL Server All Errors List
SELECT message_id, severity, text
FROM sys.messages
WHERE language_id = 1033; /* assuming US English */
Your language_id
you can find in
sys.syslanguages
system view, column msglangid
:
langid | dateformat | datefirst | upgrade | name | alias | months | shortmonths | days | lcid | msglangid |
---|---|---|---|---|---|---|---|---|---|---|
0 | mdy | 7 | 0 | us_english | English | January,February,March,April,May,June,July,August,September,October,November,December | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 1033 | 1033 |
1 | dmy | 1 | 0 | Deutsch | German | Januar,Februar,März,April,Mai,Juni,Juli,August,September,Oktober,November,Dezember | Jan,Feb,Mär,Apr,Mai,Jun,Jul,Aug,Sep,Okt,Nov,Dez | Montag,Dienstag,Mittwoch,Donnerstag,Freitag,Samstag,Sonntag | 1031 | 1031 |
2 | dmy | 1 | 0 | Français | French | janvier,février,mars,avril,mai,juin,juillet,août,septembre,octobre,novembre,décembre | janv,févr,mars,avr,mai,juin,juil,août,sept,oct,nov,déc | lundi,mardi,mercredi,jeudi,vendredi,samedi,dimanche | 1036 | 1036 |
3 | ymd | 7 | 0 | 日本語 | Japanese | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 月曜日,火曜日,水曜日,木曜日,金曜日,土曜日,日曜日 | 1041 | 1041 |
4 | dmy | 1 | 0 | Dansk | Danish | januar,februar,marts,april,maj,juni,juli,august,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,aug,sep,okt,nov,dec | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 1030 | 1030 |
5 | dmy | 1 | 0 | Español | Spanish | Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubre,Noviembre,Diciembre | Ene,Feb,Mar,Abr,May,Jun,Jul,Ago,Sep,Oct,Nov,Dic | Lunes,Martes,Miércoles,Jueves,Viernes,Sábado,Domingo | 3082 | 3082 |
6 | dmy | 1 | 0 | Italiano | Italian | gennaio,febbraio,marzo,aprile,maggio,giugno,luglio,agosto,settembre,ottobre,novembre,dicembre | gen,feb,mar,apr,mag,giu,lug,ago,set,ott,nov,dic | lunedì,martedì,mercoledì,giovedì,venerdì,sabato,domenica | 1040 | 1040 |
7 | dmy | 1 | 0 | Nederlands | Dutch | januari,februari,maart,april,mei,juni,juli,augustus,september,oktober,november,december | jan,feb,mrt,apr,mei,jun,jul,aug,sep,okt,nov,dec | maandag,dinsdag,woensdag,donderdag,vrijdag,zaterdag,zondag | 1043 | 1043 |
8 | dmy | 1 | 0 | Norsk | Norwegian | januar,februar,mars,april,mai,juni,juli,august,september,oktober,november,desember | jan,feb,mar,apr,mai,jun,jul,aug,sep,okt,nov,des | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 2068 | 2068 |
9 | dmy | 7 | 0 | Português | Portuguese | janeiro,fevereiro,março,abril,maio,junho,julho,agosto,setembro,outubro,novembro,dezembro | jan,fev,mar,abr,mai,jun,jul,ago,set,out,nov,dez | segunda-feira,terça-feira,quarta-feira,quinta-feira,sexta-feira,sábado,domingo | 2070 | 2070 |
10 | dmy | 1 | 0 | Suomi | Finnish | tammikuuta,helmikuuta,maaliskuuta,huhtikuuta,toukokuuta,kesäkuuta,heinäkuuta,elokuuta,syyskuuta,lokakuuta,marraskuuta,joulukuuta | tammi,helmi,maalis,huhti,touko,kesä,heinä,elo,syys,loka,marras,joulu | maanantai,tiistai,keskiviikko,torstai,perjantai,lauantai,sunnuntai | 1035 | 1035 |
11 | ymd | 1 | 0 | Svenska | Swedish | januari,februari,mars,april,maj,juni,juli,augusti,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,aug,sep,okt,nov,dec | måndag,tisdag,onsdag,torsdag,fredag,lördag,söndag | 1053 | 1053 |
12 | dmy | 1 | 0 | čeština | Czech | leden,únor,březen,duben,květen,červen,červenec,srpen,září,říjen,listopad,prosinec | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | pondělí,úterý,středa,čtvrtek,pátek,sobota,neděle | 1029 | 1029 |
13 | ymd | 1 | 0 | magyar | Hungarian | január,február,március,április,május,június,július,augusztus,szeptember,október,november,december | jan,febr,márc,ápr,máj,jún,júl,aug,szept,okt,nov,dec | hétfő,kedd,szerda,csütörtök,péntek,szombat,vasárnap | 1038 | 1038 |
14 | dmy | 1 | 0 | polski | Polish | styczeń,luty,marzec,kwiecień,maj,czerwiec,lipiec,sierpień,wrzesień,październik,listopad,grudzień | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | poniedziałek,wtorek,środa,czwartek,piątek,sobota,niedziela | 1045 | 1045 |
15 | dmy | 1 | 0 | română | Romanian | ianuarie,februarie,martie,aprilie,mai,iunie,iulie,august,septembrie,octombrie,noiembrie,decembrie | Ian,Feb,Mar,Apr,Mai,Iun,Iul,Aug,Sep,Oct,Nov,Dec | luni,marţi,miercuri,joi,vineri,sîmbătă,duminică | 1048 | 1048 |
16 | ymd | 1 | 0 | hrvatski | Croatian | siječanj,veljača,ožujak,travanj,svibanj,lipanj,srpanj,kolovoz,rujan,listopad,studeni,prosinac | sij,vel,ožu,tra,svi,lip,srp,kol,ruj,lis,stu,pro | ponedjeljak,utorak,srijeda,četvrtak,petak,subota,nedjelja | 1050 | 1050 |
17 | dmy | 1 | 0 | slovenčina | Slovak | január,február,marec,apríl,máj,jún,júl,august,september,október,november,december | I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII | pondelok,utorok,streda,štvrtok,piatok,sobota,nedeľa | 1051 | 1051 |
18 | dmy | 1 | 0 | slovenski | Slovenian | januar,februar,marec,april,maj,junij,julij,avgust,september,oktober,november,december | jan,feb,mar,apr,maj,jun,jul,avg,sept,okt,nov,dec | ponedeljek,torek,sreda,četrtek,petek,sobota,nedelja | 1060 | 1060 |
19 | dmy | 1 | 0 | ελληνικά | Greek | Ιανουαρίου,Φεβρουαρίου,Μαρτίου,Απριλίου,Μα_ου,Ιουνίου,Ιουλίου,Αυγούστου,Σεπτεμβρίου,Οκτωβρίου,Νοεμβρίου,Δεκεμβρίου | Ιαν,Φεβ,Μαρ,Απρ,Μαϊ,Ιουν,Ιουλ,Αυγ,Σεπ,Οκτ,Νοε,Δεκ | Δευτέρα,Τρίτη,Τετάρτη,Πέμπτη,Παρασκευή,Σάββατο,Κυριακή | 1032 | 1032 |
20 | dmy | 1 | 0 | български | Bulgarian | януари,февруари,март,април,май,юни,юли,август,септември,октомври,ноември,декември | януари,февруари,март,април,май,юни,юли,август,септември,октомври,ноември,декември | понеделник,вторник,сряда,четвъртък,петък,събота,неделя | 1026 | 1026 |
21 | dmy | 1 | 0 | русский | Russian | Январь,Февраль,Март,Апрель,Май,Июнь,Июль,Август,Сентябрь,Октябрь,Ноябрь,Декабрь | янв,фев,мар,апр,май,июн,июл,авг,сен,окт,ноя,дек | понедельник,вторник,среда,четверг,пятница,суббота,воскресенье | 1049 | 1049 |
22 | dmy | 1 | 0 | Türkçe | Turkish | Ocak,Şubat,Mart,Nisan,Mayıs,Haziran,Temmuz,Ağustos,Eylül,Ekim,Kasım,Aralık | Oca,Şub,Mar,Nis,May,Haz,Tem,Ağu,Eyl,Eki,Kas,Ara | Pazartesi,Salı,Çarşamba,Perşembe,Cuma,Cumartesi,Pazar | 1055 | 1055 |
23 | dmy | 1 | 0 | British | British English | January,February,March,April,May,June,July,August,September,October,November,December | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 2057 | 1033 |
24 | dmy | 1 | 0 | eesti | Estonian | jaanuar,veebruar,märts,aprill,mai,juuni,juuli,august,september,oktoober,november,detsember | jaan,veebr,märts,apr,mai,juuni,juuli,aug,sept,okt,nov,dets | esmaspäev,teisipäev,kolmapäev,neljapäev,reede,laupäev,pühapäev | 1061 | 1061 |
25 | ymd | 1 | 0 | latviešu | Latvian | janvāris,februāris,marts,aprīlis,maijs,jūnijs,jūlijs,augusts,septembris,oktobris,novembris,decembris | jan,feb,mar,apr,mai,jūn,jūl,aug,sep,okt,nov,dec | pirmdiena,otrdiena,trešdiena,ceturtdiena,piektdiena,sestdiena,svētdiena | 1062 | 1062 |
26 | ymd | 1 | 0 | lietuvių | Lithuanian | sausis,vasaris,kovas,balandis,gegužė,birželis,liepa,rugpjūtis,rugsėjis,spalis,lapkritis,gruodis | sau,vas,kov,bal,geg,bir,lie,rgp,rgs,spl,lap,grd | pirmadienis,antradienis,trečiadienis,ketvirtadienis,penktadienis,šeštadienis,sekmadienis | 1063 | 1063 |
27 | dmy | 7 | 0 | Português (Brasil) | Brazilian | Janeiro,Fevereiro,Março,Abril,Maio,Junho,Julho,Agosto,Setembro,Outubro,Novembro,Dezembro | Jan,Fev,Mar,Abr,Mai,Jun,Jul,Ago,Set,Out,Nov,Dez | Segunda-Feira,Terça-Feira,Quarta-Feira,Quinta-Feira,Sexta-Feira,Sábado,Domingo | 1046 | 1046 |
28 | ymd | 7 | 0 | 繁體中文 | Traditional Chinese | 一月,二月,三月,四月,五月,六月,七月,八月,九月,十月,十一月,十二月 | 01,02,03,04,05,06,07,08,09,10,11,12 | 星期一,星期二,星期三,星期四,星期五,星期六,星期日 | 1028 | 1028 |
29 | ymd | 7 | 0 | 한국어 | Korean | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 월요일,화요일,수요일,목요일,금요일,토요일,일요일 | 1042 | 1042 |
30 | ymd | 7 | 0 | 简体中文 | Simplified Chinese | 01,02,03,04,05,06,07,08,09,10,11,12 | 01,02,03,04,05,06,07,08,09,10,11,12 | 星期一,星期二,星期三,星期四,星期五,星期六,星期日 | 2052 | 2052 |
31 | dmy | 1 | 0 | Arabic | Arabic | Muharram,Safar,Rabie I,Rabie II,Jumada I,Jumada II,Rajab,Shaaban,Ramadan,Shawwal,Thou Alqadah,Thou Alhajja | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday | 1025 | 1025 |
32 | dmy | 7 | 0 | ไทย | Thai | มกราคม,กุมภาพันธ์,มีนาคม,เมษายน,พฤษภาคม,มิถุนายน,กรกฎาคม,สิงหาคม,กันยายน,ตุลาคม,พฤศจิกายน,ธันวาคม | ม.ค.,ก.พ.,มี.ค.,เม.ย.,พ.ค.,มิ.ย.,ก.ค.,ส.ค.,ก.ย.,ต.ค.,พ.ย.,ธ.ค. | จันทร์,อังคาร,พุธ,พฤหัสบดี,ศุกร์,เสาร์,อาทิตย์ | 1054 | 1054 |
33 | dmy | 1 | 0 | norsk (bokmål) | Bokmål | januar,februar,mars,april,mai,juni,juli,august,september,oktober,november,desember | jan,feb,mar,apr,mai,jun,jul,aug,sep,okt,nov,des | mandag,tirsdag,onsdag,torsdag,fredag,lørdag,søndag | 1044 | 1044 |
Levels of Severity
Severity level | Description |
---|---|
0-9 | Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9. |
10 | Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application. |
11-16 | Indicate errors that can be corrected by the user. |
11 | Indicates that the given object or entity does not exist. |
12 | A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency. |
13 | Indicates transaction deadlock errors. |
14 | Indicates security-related errors, such as permission denied. |
15 | Indicates syntax errors in the Transact-SQL command. |
16 | Indicates general errors that can be corrected by the user. |
17-19 | Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem. |
17 | Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator. |
18 | Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs. |
19 | Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log. |
20-24 | Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect. Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log. |
20 | Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged. |
21 | Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged. |
22 | Indicates that the table or index specified in the message has been damaged by a software or hardware problem. Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database. If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it. |
23 | Indicates that the integrity of the entire database is in question because of a hardware or software problem. Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database. |
24 | Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor. |
SQL Server Errors
message_id | Description | Article |
---|---|---|
0 | You may see “out of user memory quota” message in errorlog when you use In-Memory OLTP feature … | Out of user memory quota |
0 | Logon Failure: The User has not Been Granted. The operating system returned the error ????? while … | Compressed backup errors |
0 | A transport-level error has occurred when receiving results from the server. | link1 |
0 | The MSSQLSERVER service was unable to log on as SQLAuthoritySQLFarmService with the currently c … | The User has not Been Granted |
0 | A server error occurred on current command. The results, if any, should be discarded. | Who owns your availability groups? |
0 | A network-related issue or instance-specific error occured while establishing a connection to SQL Server | Cannot Connect to SQL Server |
0 | Connecting to Azure SQL Database: Requested tenant identifier ‘00000000-0000-0000-0000-000000000000’ is… | Requested tenant identifier |
102 | Incorrect syntax near ‘%.*ls’. | 102_link1,102_link2 |
120 | The select list for the INSERT statement contains fewer items than the insert list. The number of … | 120_link1 |
121 | The select list for the INSERT statement contains more items than the insert list. The number of … | 121_link1 |
131 | The size (%d) given to the %S_MSG ‘%.*ls’ exceeds the maximum allowed for any data type (%d). | 131_link1 |
145 | ORDER BY items must appear in the select list if SELECT DISTINCT is specified. | 145_link1 |
156 | Incorrect syntax near the keyword ‘ORDER’. | 156_link1 |
207 | Invalid column name ‘%.*ls’. | 207_link1 |
213 | Column name or number of supplied values does not match table definition. | 213_link1,213_link2 |
229 | The %ls permission was denied on the object ‘%.ls’, database ‘%.ls’, schema ‘%.*ls’. | 229_link1 |
241 | Conversion failed when converting date and/or time from character string. | 241_link1 |
257 | Implicit conversion from data type %ls to %ls is not allowed. Use the CONVERT function to run this query | 257_link1 |
264 | The column name ‘%.*ls’ is specified more than once in the SET clause or column list of an INSERT … | 264_link1 |
297 | The user does not have permission to perform this action. | 297_link1 |
352 | The table-valued parameter «%.*ls» must be declared with the READONLY option. | 352_link1 |
459 | Collation ‘%.*ls’ is supported on Unicode data types only and cannot be applied to char, varchar or … | 459_link1 |
535 | The datediff function resulted in an overflow. The number of dateparts separating two date/time | 535_link1 |
596 | Cannot continue execution because the session is in the kill state. | 596_link1,596_link2,596_link3 |
650 | You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels. | 650_link1 |
657 | Could not disable support for increased partitions in database … | 657_link1 |
666 | The maximum system-generated unique value for a duplicate group was exceeded for index with … | 666_link1 |
701 | There is insufficient system memory in resource pool ‘%ls’ to run this query. … | 701_link1,701_link2 |
824 | SQL Server detected a logical consistency-based I/O error … | 824_link1,824_link2,KB2152734,824_link3 |
825 | The operating system returned error %ls to SQL Server. It failed creating event for a %S_MSG at … | 825_link1 |
913 | Could Not Find Database %d. Database May Not be Activated Yet or May be in Transition … | 913_link1 |
922 | Database ‘%.*ls’ is being recovered. Waiting until recovery is finished. | 922_link1 |
926 | Database ‘%.*ls’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog … | 926_link1 |
1052 | Conflicting %ls options «%ls» and «%ls». | 1052_link1 |
1065 | The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE … | 1065_link1 |
1105 | Could not allocate space for object ‘%.ls’%.ls in database ‘%.ls’ because the ‘%.ls’ filegroup is … | 1105_link1 |
1204 | The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your … | 1204_link1 |
1205 | Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen … | 1205_link1 |
1222 | Lock request time out period exceeded. | 1222_link1 |
1219 | Your session has been disconnected because of a high priority DDL operation. | 1219_link1 |
1480 | The %S_MSG database «%.*ls» is changing roles from «%ls» to «%ls» because the mirroring session or … | 1480_link1 |
1701 | Creating or altering table %ls failed because the minimum row size would be 8061, including 10 b … | 1701_link1 |
1807 | Could not obtain exclusive lock on database ‘model’. Retry the operation later. … | 1807_link1 |
1904 | The statistics on table has 65 columns in the key list … | 1904_link1 |
1908 | Column ‘%.ls’ is partitioning column of the index ‘%.ls’. Partition columns for a unique index … | 1908_link1 |
2533 | Table error: page %S_PGID allocated to object ID %d, index ID %d, partition ID %I64d, alloc unit ID … | 2533_link1 |
2534 | Table error: page %S_PGID, whose header indicates that it is allocated to object ID %d, index ID %d, … | 2534_link1 |
2812 | Could not find stored procedure ‘%.*ls’. | 2812_link1 |
3101 | Exclusive access could not be obtained because the database is in use. … | 3101_link1 |
3154 | The backup set holds a backup of a database other than the existing … | 3154_link1 |
3241 | The media family on device ‘%ls’ is incorrectly formed. SQL Server cannot process this media fam … | 3241_link1 |
3314 | During undoing of a logged operation in database ‘%.*ls’, an error occurred at log record ID %S … | 3314_link1 |
3634 | The operating system returned the error ‘%ls’ while attempting ‘%ls’ on ‘%ls’. … | 3634_link1 |
3637 | A parallel operation cannot be started from a DAC connection. | 3637_link1 |
3743 | The database ‘%.*ls’ is enabled for database mirroring. Database mirroring must be removed befor … | 3743_link1 |
3906 | Failed to update database «%.*ls» because the database is read-only. | 3906_link1 |
3930 | The current transaction cannot be committed and cannot support operations that write to the log … | 3930_link1 |
3956 | Snapshot isolation transaction failed to start in database ‘%.*ls’ because the ALTER DATABASE command … | 3956_link1 |
3960 | Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to … | 3960_link1 |
4064 | Cannot open user default database. Login failed.Login failed. … | 4064_link1 |
4189 | Cannot convert to text/ntext or collate to ‘%.*ls’ because these legacy LOB types do not support UTF-8 … | 4189_link1 |
4353 | Conflicting file relocations have been specified for file ‘%.*ls’. Only a single WITH MOVE clause … | 4353_link1 |
4629 | Permissions on server scoped catalog views or system stored procedures or extended stored … | 4629_link1 |
4901 | ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition … | 4901_link1 |
4922 | ALTER TABLE ALTER COLUMN Address failed because one or more objects access this column. … | 4922_link1 |
4934 | Computed column ‘%.ls’ in table ‘%.ls’ cannot be persisted because the column does user or … | 4934_link1 |
4947 | ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘%.*ls’ for the … | 4947_link1 |
5004 | To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. | 5004_link1 |
5011 | User does not have permission to alter database ‘%.*ls’, the database does not exist, or the database … | 5011_link1 |
5061 | ALTER DATABASE failed because a lock could not be placed on database ‘%.*ls’. Try again later. | 5061_link1 |
5084 | Setting database option %ls to %ls for database ‘%.*ls’. | 5084_link1 |
5120 | Unable to open the physical file … Operating system error 5: «5(Access is denied.)» … | SQL SERVER — FIX Error 5120 |
5123 | CREATE FILE encountered operating system error «%ls»(The system cannot find the path specified.) … | 5123_link1, 5123_link2 |
5171 | %.*ls is not a primary database file. | 5171_link1 |
5172 | The header for file ‘%ls’ is not a valid database file header. The %ls property is incorrect. | 5172_link1 |
5235 | %lsDBCC %ls (%ls%ls%ls)%ls executed by %ls terminated abnormally due to error state %d. Elapsed time: … | 5235_link1 |
5846 | Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two … | 5846_link1 |
6335 | XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. | 6335_link1 |
6348 | Specified collection ‘%.*ls’ cannot be created because it already exists or you do not have permission. | 6348_link1 |
6401 | Cannot roll back %.*ls. No transaction or savepoint of that name was found. | 6401_link1 |
7341 | Cannot get the current row value of column «%ls.%ls» from OLE DB provider «%ls» for linked server «%ls … | 7341_link1 |
7344 | The OLE DB provider «%ls» for linked server «%ls» could not %ls table «%ls» because of column … | 7344_link1 |
7356 | The OLE DB provider «%ls» for linked server «%ls» supplied inconsistent metadata for a column. … | 7356_link1 |
7357 | Cannot process the object «%ls». The OLE DB provider «%ls» for linked server «%ls» indicates that … | 7357_link1, 7357_link2 |
7391 | The operation could not be performed because OLE DB provider «%ls» for linked server «%ls» … … | 7391_link2 |
7719 | CREATE/ALTER partition function failed as only maximum of 1000 partitions can be created. … | 657_link1 |
7926 | Check statement aborted. The database could not be checked as a database snapshot could not be created … | 7926_link1 |
8101 | An explicit value for the identity column in table ‘%.*ls’ can only be specified when a column list is … | 8101_link1 |
8107 | IDENTITY_INSERT is already ON for table ‘%.ls.%.ls.%.*ls’. Cannot perform SET operation for table ‘% … | 8107_link1 |
8115 | Arithmetic overflow error converting %ls to data type %ls. | 8115_link1 |
8116 | Argument data type %ls is invalid for argument %d of %ls function. | 8116_link1 |
8117 | Operand data type %ls is invalid for %ls operator. | 8117_link1 |
8180 | Statement(s) could not be prepared. | 8180_link1 |
8127 | Column «%.ls.%.ls» is invalid in the ORDER BY clause because it is not contained in either an … | 8127_link1 |
8152 | String or binary data would be truncated. | 8152_link1 |
8624 | Internal Query Processor Error: The query processor could not produce a query plan. | 8624_link1 |
8645 | A timeout occurred while waiting for memory resources to execute the query in resource pool ‘%ls’ (%ld … | 8645_link1 |
8651 | Could not perform the operation because the requested memory grant was not available in resource … | 8651_link1 |
8672 | The MERGE statement attempted to UPDATE or DELETE the same row more than once… … | 8672_link1 |
8909 | Table error: Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), pa … | 8909_link1 |
8921 | Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or … | 8921_link1 |
8928 | Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls): Page %S_PGID could not… | 8928_link1 |
8939 | Table error: Object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page … | 8939_link1 |
8948 | Database error: Page %S_PGID is marked with the wrong type in PFS page %S_PGID. PFS status 0x%x … | 8948_link1 |
9001 | The log for database ‘%.*ls’ is not available. Check the operating system error log for related … | 9001_link1 |
9002 | The transaction log for database ‘%ls’ is full due to ‘%ls’. … | 9002_link1,9002_link2,9002_link3 |
9105 | The provided statistics stream is corrupt. | 9105_link1 |
9642 | An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: %i, … | 9105_link1 |
10314 | An error occurred in the Microsoft .NET Framework while trying to load assembly id %d. The server may … | 10314_link1,10314_link2 |
10637 | Cannot perform this operation on ‘%.*ls’ with ID %I64d as one or more indexes are currently in … | 10637_link1 |
10794 | The %S_MSG ‘%ls’ is not supported with %S_MSG. | 10794_link1,10794_link2 |
11442 | Columnstore index creation is not supported in tempdb when memory-optimized metadata mode is enabled. … | [11442_link1][51] |
11535 | EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), and the … | 11535_link1 |
12349 | Operation not supported for memory optimized tables having columnstore index. | 12349_link1 |
13609 | JSON text is not properly formatted. Unexpected character ‘%lc’ is found at position %d. | 13609_link1 |
13515 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ has custom unique keys defined. … | 13515_link1 |
13518 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ has IDENTITY column specification … | 13518_link1 |
13523 | Setting SYSTEM_VERSIONING to ON failed because table ‘%.ls’ has %d columns and table ‘%.ls’ has %d … | 13523_link1 |
13543 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ contains invalid records with end … | 13543_link1 |
13570 | The use of replication is not supported with system-versioned temporal table ‘%s’ | 13570_link1 |
13573 | Setting SYSTEM_VERSIONING to ON failed because history table ‘%.*ls’ contains overlapping records. | 13573_link1 |
13575 | ADD PERIOD FOR SYSTEM_TIME failed because table ‘%.*ls’ contains records where end of period is not … | 13575_link1 |
13901 | Identifier ‘%.*ls’ in a MATCH clause is not a node table or an alias for a node table. | 13901_link1 |
13902 | Identifier ‘%.*ls’ in a MATCH clause is not an edge table or an alias for an edge table. | 13902_link1 |
15002 | The procedure ‘sys.sp_dbcmptlevel’ cannot be executed within a transaction. … | 15002_link1 |
15021 | Invalid value given for parameter %s. Specify a valid parameter value. | 15021_link1 |
15136 | The database principal is set as the execution context of one or more procedures, functions, … | 15136_link1 |
15190 | There are still remote logins or linked logins for the server ‘%s’. | 15190_link1 |
15199 | The current security context cannot be reverted. Please switch to the original database where … | 15199_link1 |
15274 | Access to the remote server is denied because the current security context is not trusted. | 15274_link1 |
15404 | Could not obtain information about Windows NT group/user ‘%ls’, error code %#lx. | 15404_link1 |
15406 | Cannot execute as the server principal because the principal «%.*ls» does not exist, this type of … | 15406_link1 |
15457 | Configuration option ‘%ls’ changed from %ld to %ld. Run the RECONFIGURE statement to install. | 5457_link1 |
17051 | SQL Server evaluation edition has expired. | 17051_link1 |
17182 | TDSSNIClient initialization failed with error 0x%lx, status code 0x%lx. Reason: %S_MSG %.*ls | 17182_link1 |
17190 | Initializing the FallBack certificate failed with error code: %d, state: %d, error number: %d. … | 17190_link1 |
17300 | SQL Server was unable to run a new system task, either because there is insufficient memory or the … | 17300_link1 |
17836 | Length specified in network packet payload did not match number of bytes read; the connection has been … | 17836_link1 |
18054 | Error %d, severity %d, state %d was raised, but no message with that error number was found in … | 18054_link1 |
18272 | During restore restart, an I/O error occurred on checkpoint file ‘%s’ (operating system error %s … | 18272_link1 |
18357 | Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated … | 18357_link1 |
18401 | Login failed for user ‘%.*ls’. Reason: Server is in script upgrade mode. Only administrator can connect… | 18401_link1 |
18452 | Login failed. The login is from an untrusted domain and cannot be used with Windows authenticati … | 18452_link1 |
18456 | Login failed for user ‘%.ls’.%.ls%.*ls | 18456_link1 |
20598 | The row was not found at the Subscriber when applying the replicated %S_MSG command for Table ‘%s’ with… | 20598_link1 |
22911 | The capture job cannot be used by Change Data Capture to extract changes from the log when … | 22911_link1 |
25713 | The value specified for %S_MSG, «%.ls», %S_MSG, «%.ls», is invalid. | 25713_link1,25713_link2 |
26023 | Server TCP provider failed to listen on [ %s <%s> %d]. Tcp port is already in use. | 26023_link1 |
33111 | Cannot find server %S_MSG with thumbprint ‘%.*ls’. | 33111_link1 |
33206 | SQL Server Audit failed to create the audit file ‘%s’. Make sure that the disk is not full and … | 33206_link1 |
35217 | The thread pool for Always On Availability Groups was unable to start a new worker thread because … | [35217_link1] |
35250 | The connection to the primary replica is not active. The command cannot be processed. | 35250_link1 |
35264 | Always On Availability Groups data movement for database ‘%.*ls’ has been suspended for the following … | 35264_link1 |
35320 | Column store indexes are not allowed on tables for which the durability option SCHEMA_ONLY is specified. | 35320_link1 |
35337 | UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. … | 35337_link1 |
35343 | The statement failed. Column ‘%.*ls’ has a data type that cannot participate in a columnstore index. | 35343_link1 |
39004 | A ‘%s’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x%x. | 39004_link1 |
41121 | The local availability replica of availability group ‘%.*ls’ cannot accept signal ‘%s’ in its current … | 41121_link1 |
41317 | A user transaction that accesses memory optimized tables or natively compiled modules cannot access more… | [41317_link1][51] |
41922 | The backup operation for a database with service-managed transaprent data encryption is not supported on… | 41922_link1 |
[51]:
[35217_link1] :https://www.seangallardy.com/error-35217-and-availability-groups-smh/
List of error messages between 1 and 999 in SQL Server 2017.
These error messages are all available by querying the sys.messages
catalog view on the master
database.
Содержание статьи:
-
- SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
- Ошибка SQL-сервера 26
- Ошибка SQL-сервера 18456
- Не удалось запустить SQL-server — код ошибки 3417
- Повреждена база данных
- Код ошибки SQL-сервера 945
- Код ошибки SQL-сервера 5172
- Ошибка SQL-сервера 823
- Ошибка SQL-сервера 8946
- Другие ошибки SQL Server
- Код ошибки SQL-сервера 1814
- Код ошибки SQL-сервера 1067
- SQL-сервер запускается, но работает слишком медленно
- SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
SQL-сервер не найден или недоступен, ошибки соединения с SQL-сервером
- Если SQL-сервер не найден, убедитесь, что ваш экземпляр SQL-сервера действительно установлен и запущен. Для этого зайдите на компьютер, где он установлен, запустите диспетчер конфигурации SQL и проверьте, есть ли там тот экземпляр, к которому вы пытаетесь подключиться и запущен ли он. Нелишним будет также получить отчет об обнаружении компонентов SQL-серверов.
- Если вы проделали п1. и не обнаружили источник проблемы, возможно, неверно указан IP-адрес компьютера или номер порта TCP. Перепроверьте их настройки.
- Причиной того, что невозможно подключиться к SQL-серверу, также может быть сеть, убедитесь, что компьютер с SQL-сервером доступен по сети.
- Проверьте, может ли клиентское приложение, установленное на том же компьютере, что и сервер, подключиться к SQL-серверу. Запустите SQL Server Management Studio(SSMS), в диалоговом окне “Подключиться к серверу” выберите тип сервера Database Engine, укажите способ аутентификации “Аутентификация Windows”, введите имя компьютера и экземпляра SQL-сервера. Проверьте подключение.
Обратите внимание, что многие сообщения об ошибках могут быть не показаны или не содержат достаточной информации для устранения проблемы. Это сделано из соображений безопасности, чтобы при попытке взлома злоумышленники не могли получить информацию об SQL-сервере. Полные сведения содержатся в логе ошибок, который обычно хранится по адресу C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLLogERRORLOG, или там, куда его поместил администратор системы.
Ошибка SQL-сервера 26
Одна из наиболее часто встречающихся ошибок подключения к SQL-серверу, обычно связана с тем, что в настройках SQL-сервера не разрешены или ограничены удаленные соединения. Чтобы это исправить, попробуйте:
- в SSMS в настройках SQL-сервера включите аутентификацию Windows
- для брандмауэра Windows создайте новое правило, которое разрешает подключение для всех программ и протоколов с указанного IP-адреса
- убедитесь, что запущена служба SQL Server Browser
Ошибка SQL-сервера 18456
Эта ошибка означает, что попытка подключиться к серверу не успешна из-за проблем с именем пользователя или паролем. По коду ошибки в журнале ошибок можно узнать более точную причину, чтобы устранить ее.
Не удалось запустить SQL-server — код ошибки 3417
Возникает в случае, если были изменены настройки Windows или перемещена папка с файлами MSSQL.
- зайдите в C:Program FilesMicrosoft SQLServerMSSQL.1MSSqLData — БезопасностьНастройки доступа — Учетная запись сетевой службы — добавьте учетную запись сетевой службы
- проверьте, что MDF-файл не сжимается. Если это не так, отключите “Сжимать содержимое для экономии места на диске” в свойствах файла
Иногда ни один из этих способов не помогает, это значит, что файлы БД повреждены и ее придется восстанавливать из резервной копии.
Повреждена база данных
Код ошибки SQL-сервера 945
Ошибка 945 возникает, когда БД SQL-сервера помечена как IsShutdown. Проверьте, достаточно ли места на диске, достаточно ли прав у учетной записи для операций с БД, файлы MDF и LDF не должны быть помечены “Только для чтения”.
Код ошибки SQL-сервера 5172
SQL-сервер хранит свою физическую БД в первичном файле, в котором информация разбита постранично. Первая страница содержит информацию о заголовке mdf-файла и называется страницей заголовка. Она состоит из разнообразной информации о БД, такой как размер файла, подпись и т.д. В процессе прикрепления MDF на SQL-сервере часто возникает ошибка 5172. Это в основном происходит, если MDF-файл поврежден, информация в его заголовке тоже и соответственно сложно добраться до данных. Причиной может быть вирус, аварийное выключение системы, ошибка оборудования.
Ошибка SQL-сервера 823
SQL использует API Windows для операций ввода-вывода, но кроме завершения этих операций SQL проверяет все ошибки обращений к API. Если эти обращения несовместимы с ОС, появляется ошибка 823. Сообщение об ошибке 823 означает, что существует проблема с базовым оборудованием для хранения данных или с драйвером, который находится на пути запроса ввода-вывода. Пользователи могут столкнуться с этой ошибкой, если в файловой системе есть противоречия или поврежден файл базы данных.
Ошибка SQL-сервера 8946
Основной причиной ошибки 8946 так же, как и для 5172, является повреждение заголовков страниц БД SQL вследствие сбоя питания, вирусной атаки, отказа оборудования — SQL-сервер больше не может прочесть эти страницы.
Перечисленные ошибки 945, 5172, 823, 8946 можно устранить двумя методами:
- если у вас есть свежая резервная копия базы — восстановить базу из этой копии
- можно попробовать использовать специализированное ПО, такое как SQL Recovery Tool, чтобы восстановить поврежденные файлы
Желательно определить, что именно привело к возникновению ошибок и принять меры, чтобы это не повторялось — заменить плохо работающее оборудование, повысить информационную безопасность.
Другие ошибки SQL
Код ошибки SQL-сервера 1814
SQL-сервер не может создать базу данных tempdb. Убедитесь, что на выделенном под нее диске достаточно места и что у учетной записи хватает прав для записи в указанную директорию.
Код ошибки SQL-сервера 1067
Эта ошибка может возникать по разным причинам. Наиболее часто оказывается, что повреждены или отсутствуют конфигурационные файлы, SQL-сервер обращается к поврежденным системным файлам, ошибочные данные пользователя, нет информации про лицензию. В самых тяжелых случаях придется переустанавливать SQL-сервер. Но иногда помогает восстановление поврежденных файлов или изменение настроек SQL-сервера — вы можете создать новую учетную запись в домене и использовать ее для службы MSSQL.
SQL-сервер запускается, но работает слишком медленно
Проанализируйте журнал сервера, индексы (фрагментацию), запросы, задания, возможность взаимных блокировок. Причин может быть масса.
Мы работаем с разными версиями SQL-сервера уже много лет, знакомы со всевозможными инструкциями SQL-сервера, видели самые разные варианты его настройки и использования на проектах у своих клиентов. В целом мы можем выделить четыре основных источника неполадок:
- Индексы — причина проблем номер один. Неправильные индексы, отсутствующие индексы, слишком много индексов и подобное. Чаще всего при проблеме с индексами пользователи или администраторы базы данных не получают сообщения об ошибке, они просто видят, что база работает очень медленно и докопаться до причин бывает очень нелегко
- изначально плохая архитектура сервера баз данных — ошибка, которую очень сложно и дорого исправлять на этапе, когда база уже используется
- плохой код, в котором возможны блокировки и тупиковые места
- использование конфигурации по умолчанию,
Если у вас не получается устранить ошибки сервера SQL-server самостоятельно, если они появляются снова и снова, то скорее всего в основе лежит одна из этих причин. В таком случае — если у вас произошла ошибка с SQL сервером, ваше ПО не видит SQL-сервер, либо нужно развернуть кластер SQL-серверов — вы всегда можете обратиться за консультацией и технической поддержкой к специалистам Интегруса, отправив заявку с сайта, написав на e-mail, либо позвонив в колл-центр нашей компании.
Присоединяйтесь к нам,
чтобы получать чек-листы, реальные кейсы, а также
обзоры сервисов раз в 2 недели.