Ошибка sql сервера что это

Содержание статьи:

• • 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-серверов.
• Если вы проделали п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.

Перечисленные ошибки 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, либо позвонив в колл-центр нашей компании.

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.

С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).

С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

SELECT message_id, severity, text
  FROM sys.messages
 WHERE language_id = 1033; /* assuming US English */

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.

С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).

С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

---

Abstract

This topic was updated and moved into SQL Server Books Online in June. 2016. See
https://msdn.microsoft.com/library/mt750266.aspx 

Original topic follows:

This is an exhaustive list of troubleshooting techniques to use when you cannot connect to the SQL Server Database Engine. These steps are not in the order of the most likely problems which you probably already tried. These steps
are in order of the most basic problems to more complex problems. These steps assume that you are connecting to SQL Server from another computer by using the TCP/IP protocol, which is the most common situation. These steps are written for SQL Server 2008 R2
with a client running Windows 7, however the steps generally apply to other versions of SQL Server and other operating systems with only slight modifications.

These instructions are particularly useful when troubleshooting the «Connect to Server» error, which can be Error Number: 11001 (or 53), Severity: 20, State: 0

• «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)» or «(provider: TCP Provider, error: 0 — No such host is known.) (Microsoft SQL Server, Error: 11001)»

This error usually means that the SQL Server computer can’t be found or that the TCP port number is either not known, or is not the correct port number, or is blocked by a firewall.

Not included

• This topic does not include information about SSPI errors. For SSPI errors, see How to troubleshoot the «Cannot generate SSPI context» error message

• This topic does not include information about Kerberos errors. For help, see Microsoft Kerberos Configuration Manager for SQL Server.

• This topic does not include information about SQL Azure Connectivity.

---

Gathering Information about the Instance of SQL Server

1. Confirm the instance of the SQL Server Database Engine is installed and running.

   1. Logon to the computer hosting the instance of SQL Server.
   2. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
   3. Using Configuration Manager, in the left pane select SQL Server Services. In the right-pane confirm that the instance of the Database Engine is present and running. The instance named MSSQLSERVER is
      a default (unnamed) instance. There can only be one default instance. Other (named) instances will have their names listed between the parentheses. SQL Server Express uses the name SQLEXPRESS as the instance name unless someone named it something
      else during setup. Make a note of the name of the instance that you are trying to connect to. Also, confirm that the instance is running, by looking for the green arrow. If the instance has a red square, right-click the instance and then click Start.
      It should turn green.        
   4. If you are attempting to connect to a named instance, make sure the SQL Server Browser service is running.
2. Get the IP Address of the computer.
   1. On the Start menu, click Run. In the Run window type cmd, and then click OK.
   2. In the command prompt window, type ipconfig and then press enter. Make a note of the IPv4 Address and the IPv6 Address. (SQL Server can connect using the older IP version 4 protocol
      or the newer IP version 6 protocol. Your network could allow either or both. Most people start by troubleshooting theIPv4 address. It’s shorter and easier to type.)
3. Get the TCP port number used by SQL Server. In most cases you are connecting to the Database Engine from another computer using the TCP protocol.
   1. Using SQL Server Management Studio on the computer running SQL Server, connect to the instance of SQL Server. In Object Explorer, expand Management, expand SQL Server Logs, and then double-click
      the current log.
   2. In the Log Viewer, click the Filter button on the toolbar. In the Message contains text box, type server is listening on, click Apply filter, and then click OK.
   3. A message similar to Server is listening on [ ‘any’ <ipv4> 1433] should be listed. This message indicates that this instance of SQL Server is listening on all the computers IP Addresses (for IP version 4) and
      is listening to TCP port 1433. (TCP port 1433 is usually the port used by the Database Engine. Only one instance of SQL Server can use a port, so if there is more than one instance of SQL Server installed, some instances must use other port numbers.)

Note: IP address 127.0.0.1 is probably listed. It is called the loopback adapter address and can only be connected to from processes on the same computer. It can be useful for troubleshooting, but you can’t use it to connect from another
computer.

---

Enable Protocols

In many installations of SQL Server, connecting to the Database Engine from another computer is not enabled unless an administrator uses Configuration Manager to enable it. To enable connections from another computer:

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. Using Configuration Manager, in the left pane expand SQL Server Network Configuration (or SQL Server Network Configuration (32bit)), 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 you will normally
   use TCP/IP. If TCP/IP is not enabled, right-click TCP/IP, and then click Enable.
3. If you changed the enabled setting for any protocol you must 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
   click Restart.

---

Testing TCP/IP Connectivity

Connecting to SQL Server by using TCP/IP requires that Windows can establish the connection.

1. On the Start menu, click Run. In the Run window type cmd, and then click OK.
2. In the command prompt window, type ping and then the IP Address of the computer that is running SQL Server. For example, ping 192.168.1.101 using an IPv4 address, or ping fe80::d51d:5ab5:6f09:8f48%11 using
   an IPv6 address. (You must replace the numbers after ping with the IP addresses on your computer.)
3. If your network is properly configured you will receive a response such as Reply from <IP address>. If you receive an error such as «Destination host unreachable.» or «Request timed out.»
   then TCP/IP is not correctly configured. (Check that the IP address was correct and was correctly typed.) Errors at this point could indicate a problem with the client computer, the server computer, or something about the network such as a router.  For more
   information, see How to Troubleshoot Basic TCP/IP Problems.
4. Next, if the ping test succeeded using the IP address, a test that the computer name can be resolved to the TCP/IP address. On the client computer, in the command prompt window, type ping and then the computer
   name of the computer that is running SQL Server. For example, ping newofficepc.
5. If you receive an error such as «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. With the DNS cache empty, the client computer will check for the newest information about the IP address for the server computer.
6. If your network is properly configured you will receive a response such as Reply from <IP address>. 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 is not correctly configured. (For more information, see How to Troubleshoot Basic TCP/IP
   Problems.) Successful name resolution is not required to connect to SQL Server, but if the computer name cannot be resolved, then connections must be made specifying the IP address. This is not ideal, but name resolution can be fixed later.

---

Testing a Local Connection

Before troubleshooting a connection problem from another computer, first test your ability to connect from a client application on the computer that is running SQL Server. This procedure
uses SQL Server Management Studio. Management Studio might not have been installed when you installed the Database Engine. You can install Management Studio from the SQL Server CD by running setup and selecting the Management Tools option. If you are running
SQL Server Express, you can download the free SQL Server Management Studio Express from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b.
(If Management Studio is not available you can test the connection using the sqlcmd.exe utility which is installed with the Database Engine.)

1. Logon to the computer where SQL Server is installed, using a login that has permission to access SQL Server. (SQL Server 2008 installation requires at least one login to be specified as a SQL Server Administrator. If you do not
   know an administrator, see Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.)

2.  On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

3. In the Connect to Server dialog box, in the Server type box, select Database Engine. In the Authentication box, select Windows Authentication.
   In the Server name box, type one of the following:

   Connecting to:	Type:	Example:
   Default instance	The computer name	ACCNT27
   Named Instance	The computer nameinstance name	ACCNT27PAYROLL

   Note: When connecting to a 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 sometimes errors regarding pipes are encountered.

   If you receive an error at this point, you will have to resolve it before proceeding. There are many possible things that could be a problem. Your login might not be authorized to connect. Your default database might be missing.

   Note: Some error messages passed to the client intentionally do not give enough information to troubleshoot the problem. This is a security feature to avoid providing an attacker with information about SQL Server.
   To view the complete information about the error, look in the SQL Server error log. The details are provided there. If you are receiving error 18456 «Login failed for user», Books Online topic http://msdn.microsoft.com/en-us/library/cc645917.aspx contains
   additional information about error codes. And Aaron Bertrand’s blog has a very extensive list of error codes athttp://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx. 

4.  If you can connect using shared memory, test connecting using TCP. You can force a TCP connection by specifying tcp: before the name. For example:

   Connecting to:	Type:	Example:
   Default instance	tcp: The computer name	tcp:ACCNT27
   Named Instance	tcp: The computer name/instance name	tcp:ACCNT27PAYROLL

   If you can connect with shared memory but not TCP, then you must fix the TCP problem. The most likely issue is that TCP is not enabled. To enable TCP, See the Enable Protocols steps above.

5.  If your goal is to connect with an account other than an administrator account, once you can connect as an administrator, try the connection again using the Windows Authentication login or the SQL Server Authentication login that
   the client application will be using.

---

Opening a Port in the Firewall

Beginning with Windows XP Service Pack 2, the Windows firewall is turned on and will block connections from another computer. To connect using TCP/IP from another computer, on the SQL Server computer you must configure the firewall
to allow connections to the TCP port used by the Database Engine. If you are connecting to a named instance or a port other than TCP port 1433, you must also open the UDP port 1434 for the SQL Server Browser service. For step by step instruction on opening
a port in the Windows firewall, see How to: Configure a Windows Firewall for Database Engine Access.

---

Testing the Connection

Once you can connect using TCP on the same computer, it’s time to try connecting from the client computer. You could theoretically use any client application, but to avoid additional complexity, install the SQL Server Management
tools on the client and make the attempt using SQL Server Management Studio.

1. On the client computer, using SQL Server Management Studio, attempt to connect 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
doesn’t work, then you probably have one of the following problems:

• Ping of the IP address doesn’t work, indicating a general TCP configuration problem. Go back to the section Testing TCP/IP Connectivity.
• SQL Server is not listening on the TCP protocol. Go back to the section Enable Protocols.
• SQL Server is listening on a port other than the port you specified. Go back to the section Gathering Information about the Instance of SQL Server.
• The SQL Server TCP port is being blocked by the firewall. Go back to the section Opening a Port in the Firewall.

2. Once you can connect using the IP address and port number, attempt to connect using the IP address without a port number. For a default instance, just use the IP address. For a named instance, use the IP address and the instance
name in the format IP address backslash instance name, for example 192.168.1.101PAYROLL If this doesn’t work, then you probably have one of the following problems:

• If you are connecting to the default instance, it might be listening on a port other than TCP port 1433, and the client isn’t attempting to connect to the correct port number.  
• If you are connecting to a named instance, the port number is not being returned to the client. 

Both of these problems are related to the SQL Server Browser service, which provides the port number to the client. The solutions are:

• Start the SQL Server Browser service. Go back to the section Gathering Information about the Instance of SQL Server, section 1.b.
• The SQL Server Browser service is being blocked by the firewall. Open UDP port 1434 in the firewall. Go back to the section Opening a Port in the Firewall.
• The UDP port 1434 information is being blocked by a router. UDP communication (datagrams) are not designed to pass through routers. This keeps the network from getting filled with low priority traffic. You might be able to configure your router to forward
  UDP traffic, or you can decide to always provide the port number when you connect.
• If the client computer is using Window 7 or Windows Server 2008, (or a more recent operating system,) the UDP traffic might be dropped by the client operating system because the response from the server is returned from a different IP address than was queried.
  This is a security feature blocking «loose source mapping.» For more information, see the Multiple Server IP Addresses section of the Books Online topic Troubleshooting: Timeout
  Expired. You might be able to configure the client to use the correct IP address, or you can decide to always provide the port number when you connect.

3. Once you can connect using the IP address  (or IP address and instance name), attempt to connect using the computer name (or computer name and instance name). Put tcp: in front of the computer name to force
a TCP/IP connection. For example, for a default instance use something like tcp:ACCNT27 For a named instance usesomething like tcp:ACCNT27PAYROLL If you could connect using the IP address but not using the computer name,
then you have a name resolution problem. Go back to the section Testing TCP/IP Connectivity, section 4.

4. Once you can connect using the computer name forcing TCP, attempt connecting using the computer name but not forcing TCP. For example, for a default instance use just the computer name such as ACCNT27 For a
named instance use the computer name and instance name like ACCNT27PAYROLL If you could connect using whileforcing TCP, but not without forcing TCP, then the client is probably using another protocol (such as named pipes).

a. On the client computer, using SQL Server Configuration Manager, in the left-pane expand SQL Native Client 10.0 Configuration, and then select Client Protocols.

b. On the right-pane, Make sure TCP/IP is enabled. If TCP/IP is disabled, right-click TCP/IP and then click Enable.

c. Make sure that the protocol order for TCP/IP is a smaller number that the named pipes or VIA 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, except that shared memory is skipped when the connection is not to the same computer. 

---

See Also

Another important place to find an extensive amount of SQL Server General & Database Engine related articles is the TechNet Wiki itself. The best entry point is SQL
Server General & Database Engine Resources on the TechNet Wiki

---

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	ms.custom
Troubleshoot common connection issues to Azure SQL Database	Provides steps to troubleshoot Azure SQL Database connection issues and resolve other Azure SQL Database or Azure SQL Managed Instance specific issues	suresh-kandoth	sureshka	wiassaf, mathoma, vanto	11/13/2022	sql-db-mi	connect	troubleshooting	OKR 11/2019 sqldbrb=1

Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance

[!INCLUDEappliesto-sqldb-sqlmi]

You receive error messages when the connection to Azure SQL Database or Azure SQL Managed Instance fails. These connection problems can be caused by reconfiguration, firewall settings, a connection timeout, incorrect login information, or failure to apply best practices and design guidelines during the application design process. Additionally, if the maximum limit on some Azure SQL Database or SQL Managed Instance resources is reached, you can no longer connect.

[!NOTE]
You can use Azure SQL Connectivity Checker to detect and fix a wide variety of connectivity errors to Azure SQL Database, Azure SQL Managed Instance and in Azure Synapse Analytics environments.

Transient fault error messages (40197, 40613 and others)

The Azure infrastructure has the ability to dynamically reconfigure servers when heavy workloads arise in the SQL Database service. This dynamic behavior might cause your client program to lose its connection to the database or instance. This kind of error condition is called a transient fault. Database reconfiguration events occur because of a planned event (for example, a software upgrade) or an unplanned event (for example, a process crash, or load balancing). Most reconfiguration events are generally short-lived and should be completed in less than 60 seconds at most. However, these events can occasionally take longer to finish, such as when a large transaction causes a long-running recovery. The following table lists various transient errors that applications can receive when connecting to Azure SQL Database.

List of transient fault error codes

Error code	Severity	Description
926	14	Database ‘replicatedmaster’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. This error may be logged on SQL Managed Instance errorlog, for a short period of time, during the last stage of a reconfiguration, while the old primary is shutting down its log. Other, non-transient scenarios involving this error message are described in the MSSQL Errors documentation.
4060	16	Cannot open database «%.*ls» requested by the login. The login failed. For more information, see Errors 4000 to 4999
40197	17	The service has encountered an error processing your request. Please try again. Error code %d. You receive this error when the service is down due to software or hardware upgrades, hardware failures, or any other failover problems. The error code (%d) embedded within the message of error 40197 provides additional information about the kind of failure or failover that occurred. Some examples of the error codes are embedded within the message of error 40197 are 40020, 40143, 40166, and 40540. Reconnecting automatically connects you to a healthy copy of your database. Your application must catch error 40197, log the embedded error code (%d) within the message for troubleshooting, and try reconnecting to SQL Database until the resources are available, and your connection is established again. For more information, see Transient errors.
40501	20	The service is currently busy. Retry the request after 10 seconds. Incident ID: %ls. Code: %d. For more information, see: •  Logical SQL server resource limits •  DTU-based limits for single databases •  DTU-based limits for elastic pools •  vCore-based limits for single databases •  vCore-based limits for elastic pools •  Azure SQL Managed Instance resource limits.
40613	17	Database ‘%.*ls’ on server ‘%.*ls’ is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of ‘%.*ls’. This error may occur if there is already an existing dedicated administrator connection (DAC) established to the database. For more information, see Transient errors.
49918	16	Cannot process request. Not enough resources to process request. The service is currently busy. Please retry the request later. For more information, see: •  Logical SQL server resource limits •  DTU-based limits for single databases •  DTU-based limits for elastic pools •  vCore-based limits for single databases •  vCore-based limits for elastic pools •  Azure SQL Managed Instance resource limits.
49919	16	Cannot process create or update request. Too many create or update operations in progress for subscription «%ld». The service is busy processing multiple create or update requests for your subscription or server. Requests are currently blocked for resource optimization. Query sys.dm_operation_status for pending operations. Wait until pending create or update requests are complete or delete one of your pending requests and retry your request later. If your operations seem to be stuck, wait for other in-progress operations to complete, or cancel them when possible. For example, you may be able to cancel a database copy or geo-replica creation by deleting the database or replica being created. If unable to cancel an apparently stuck operation, open a support ticket with Microsoft.
49920	16	Cannot process request. Too many operations in progress for subscription «%ld». The service is busy processing multiple requests for this subscription. Requests are currently blocked for resource optimization. Query sys.dm_operation_status for operation status. Wait until pending requests are complete or delete one of your pending requests and retry your request later. If your operations seem to be stuck, wait for other in-progress operations to complete, or cancel them when possible. For example, you may be able to cancel a database copy or geo-replica creation by deleting the database or replica being created. If unable to cancel an apparently stuck operation, open a support ticket with Microsoft.
4221	16	Login to read-secondary failed due to long wait on ‘HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING’. The replica is not available for login because row versions are missing for transactions that were in-flight when the replica was recycled. The issue can be resolved by rolling back or committing the active transactions on the primary replica. Occurrences of this condition can be minimized by avoiding long write transactions on the primary.
615	21	Could not find database ID %d, name ‘%.*ls’ . Error Code 615. This means in-memory cache is not in-sync with SQL server instance and lookups are retrieving stale database ID. SQL logins use in-memory cache to get the database name to ID mapping. The cache should be in sync with backend database and updated whenever attach and detach of database to/from the SQL server instance occurs. You receive this error when detach workflow fail to clean-up the in-memory cache on time and subsequent lookups to the database point to stale database ID. Try reconnecting to SQL Database until the resource are available, and the connection is established again. For more information, see Transient errors.

Steps to resolve transient connectivity issues

1. Check the Microsoft Azure Service Dashboard for any known outages that occurred during the time during which the errors were reported by the application.
2. Applications that connect to a cloud service such as Azure SQL Database should expect periodic reconfiguration events and implement retry logic to handle these errors instead of surfacing application errors to users.
3. As a database approaches its resource limits, it can seem to be a transient connectivity issue. See Resource limits.
4. If connectivity problems continue, or if the duration for which your application encounters the error exceeds 60 seconds or if you see multiple occurrences of the error in a given day, file an Azure support request by selecting Get Support on the Azure Support site.

Implementing Retry Logic

It is strongly recommended that your client program has retry logic so that it could reestablish a connection after giving the transient fault time to correct itself. We recommend that you delay for 5 seconds before your first retry. Retrying after a delay shorter than 5-seconds risks overwhelming the cloud service. For each subsequent retry the delay should grow exponentially, up to a maximum of 60 seconds.

For code examples of retry logic, see:

• Connect resiliently to SQL with ADO.NET
• Connect resiliently to SQL with PHP

For additional information on handling transient errors in your application review Troubleshooting transient connection errors to SQL Database

A discussion of the blocking period for clients that use ADO.NET is available in Connection Pooling (ADO.NET).

A network-related or instance-specific error occurred while establishing a connection to your server

The issue occurs if the application can’t connect to the server.

To resolve this issue, try the steps (in the order presented) in the Steps to fix common connection issues section.

The server/instance was not found or was not accessible (errors 26, 40, 10053)

Error 26: Error Locating server specified

System.Data.SqlClient.SqlException: 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)

Error 40: Could not open a connection to the 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)

Error 10053: A transport-level error has occurred when receiving results from the server

10053: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine)

These issues occur if the application can’t connect to the server.

To resolve these issues, try the steps (in the order presented) in the Steps to fix common connection issues section.

Cannot connect to server due to firewall issues

Error 40615: Cannot connect to < servername >

To resolve this issue, configure firewall settings on SQL Database through the Azure portal.

Error 5: Cannot connect to < servername >

To resolve this issue, make sure that port 1433 is open for outbound connections on all firewalls between the client and the internet.

Unable to log in to the server (errors 18456, 40531)

Login failed for user ‘< User name >’

Login failed for user '<User name>'.This session has been assigned a tracing ID of '<Tracing ID>'. Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 18456)

To resolve this issue, contact your service administrator to provide you with a valid user name and password.

Typically, the service administrator can use the following steps to add the login credentials:

1. Log in to the server by using SQL Server Management Studio (SSMS).

2. Run the following SQL query in the master database to check whether the login name is disabled:

   SELECT name, is_disabled FROM sys.sql_logins;

3. If the corresponding name is disabled, enable it by using the following statement:

   ALTER LOGIN <User name> ENABLE;

4. If the SQL login user name doesn’t exist, edit and run the following SQL query to create a new SQL login:

   CREATE LOGIN <SQL_login_name, sysname, login_name>
   WITH PASSWORD = '<password, sysname, Change_Password>';
   GO

5. In SSMS Object Explorer, expand Databases.

6. Select the database that you want to grant the user permission to.

7. Right-click Security, and then select New, User.

8. In the generated script with placeholders (sample shown below), replace template parameters by following the steps here and execute it:

   CREATE USER [<user_name, sysname, user_name>]
   FOR LOGIN [<login_name, sysname, login_name>]
   WITH DEFAULT_SCHEMA = [<default_schema, sysname, dbo>];
   GO
   
   -- Add user to the database owner role
   EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>';
   GO

   You can also use sp_addrolemember to map specific users to specific database roles.

   [!NOTE]
   In Azure SQL Database, consider the newer ALTER ROLE syntax for managing database role membership.

For more information, see Managing databases and logins in Azure SQL Database.

Connection timeout expired errors

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=29995;

System.Data.SqlClient.SqlException (0x80131904): Timeout expired

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. -> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -> System.ComponentModel.Win32Exception: The wait operation timed out

Cannot connect to < server name >

Cannot connect to <server name>.ADDITIONAL INFORMATION:Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=231; handshake=983; [Login] initialization=0; authentication=0; [Post-Login] complete=13000; (Microsoft SQL Server, Error: -2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476 The wait operation timed out

These exceptions can occur either because of connection or query issues. To confirm that this error is caused by connectivity issues, see Confirm whether an error is caused by a connectivity issue.

Connection timeouts occur because the application can’t connect to the server. To resolve this issue, try the steps (in the order presented) in the Steps to fix common connection issues section.

Network connection termination errors

SQL client libraries connect to Azure SQL Database and Azure SQL Managed Instance using the TCP network protocol. A client library uses a lower level component called TCP provider to manage TCP connections. When the TCP provider detects that a remote host has unexpectedly terminated an existing TCP connection, the client library raises an error. Because the error is a client error and not a SQL server error, there is no SQL error number included. Instead, the error number is 0, and the error message from the TCP provider is used.

Examples of network connection termination errors include:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) An existing connection was forcibly closed by the remote host

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Connection termination errors may occur because the database or elastic pool is temporarily unavailable. They may also occur because of various problems in the network infrastructure between the database server and the client application, including firewalls, network appliances, etc. These problems may be transient or permanent. As a general guidance, applications should use a fixed number of retry attempts for these errors before considering them permanent failures.

Resource governance errors

Azure SQL Database uses a resource governance implementation based on Resource Governor to enforce resource limits. Learn more about resource management in Azure SQL Database.

The most common resource governance errors are listed first with details, followed by a table of resource governance error messages.

Errors 10928 and 10936: Resource ID : 1. The request limit for the [database or elastic pool] is %d and has been reached

If the database level limit is reached, the detailed error message in this case reads: Resource ID : 1. The request limit for the database is %d and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.

If the elastic pool limit is reached, the detailed error message in this case reads: Resource ID : 1. The request limit for the elastic pool is %d and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance. Elastic pool limits are higher than database limits. They may be reached when multiple databases in the pool use a resource (such as workers) concurrently.

This error message indicates that the worker limit for the database or elastic pool has been reached. The maximum concurrent workers value for the service objective of the database or elastic pool will be present instead of the placeholder %d.

[!NOTE]
The initial offering of Azure SQL Database supported only single threaded queries. At that time, the number of requests was always equivalent to the number of workers. Error messages 10928 and 10936 in Azure SQL Database contain the wording «The request limit […] is N and has been reached» for backwards compatibility purposes. The limit reached is actually the number of workers. If your max degree of parallelism (MAXDOP) setting is equal to zero or is greater than one, the number of workers may be much higher than the number of requests, and the limit may be reached much sooner than when MAXDOP is equal to one.

Learn more about Sessions, workers, and requests.

Connect with the Dedicated Admin Connection (DAC) if needed

If a live incident is ongoing where the worker limit has been approached or reached, you may receive Error 10928 when you connect using SQL Server Management Studio (SSMS) or Azure Data Studio. One session can connect using the Diagnostic Connection for Database Administrators (DAC) even when the maximum worker threshold has been reached.

To establish a connection with the DAC from SSMS:

• From the menu, select File > New > Database Engine Query
• From the connection dialog box in the Server Name field, enter admin:<fully_qualified_server_name> (this will be something like admin:servername.database.windows.net).
• Select Options >>
• Select the Connection Properties tab
• In the Connect to database: box, type the name of your database
• Select Connect.

If you receive Error 40613, Database '%.*ls' on server '%.*ls' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '%.*ls', this may indicate that another session is already connected to the DAC. Only one session may connect to the DAC for a single database or an elastic pool at a time.

If you encounter the error ‘Failed to connect to server’ after selecting Connect, the DAC session may still have been established successfully if you are using a version of SSMS prior to 18.9. Early versions of SSMS attempted to provide Intellisense for connections to the DAC. This failed, as the DAC supports only a single worker and Intellisense requires a separate worker.

You cannot use a DAC connection with Object Explorer.

Review your max_worker_percent usage

To find resource consumption statistics for your database for 14 days, query the sys.resource_stats system catalog view. The max_worker_percent column shows the percentage of workers used relative to the worker limit for your database. Connect to the master database on your logical server to query sys.resource_stats.

SELECT start_time, end_time, database_name, sku, avg_cpu_percent, max_worker_percent, max_session_percent 
FROM sys.resource_stats;

You can also query resource consumption statistics from the last hour from the
sys.dm_db_resource_stats dynamic management view. Connect directly to your database to query sys.dm_db_resource_stats.

SELECT end_time, avg_cpu_percent, max_worker_percent, max_session_percent
FROM sys.dm_db_resource_stats;

Lower worker usage when possible

Blocking chains can cause a sudden surge in the number of workers in a database. A large volume of concurrent parallel queries may cause a high number of workers. Increasing your max degree of parallelism (MAXDOP) or setting MAXDOP to zero can increase the number of active workers.

Triage an incident with insufficient workers by following these steps:

1. Investigate if blocking is occurring or if you can identify a large volume of concurrent workers. Run the following query to examine current requests and check for blocking when your database is returning Error 10928. You may need to connect with the Dedicated Admin Connection (DAC) to execute the query.

   SELECT
       r.session_id, r.request_id, r.blocking_session_id, r.start_time, 
       r.status, r.command, DB_NAME(r.database_id) AS database_name,
       (SELECT COUNT(*) 
           FROM sys.dm_os_tasks AS t 
           WHERE t.session_id=r.session_id and t.request_id=r.request_id) AS worker_count,
       i.parameters, i.event_info AS input_buffer,
       r.last_wait_type, r.open_transaction_count, r.total_elapsed_time, r.cpu_time,
       r.logical_reads, r.writes, s.login_time, s.login_name, s.program_name, s.host_name
   FROM sys.dm_exec_requests as r
   JOIN sys.dm_exec_sessions as s on r.session_id=s.session_id
   OUTER APPLY sys.dm_exec_input_buffer (r.session_id,r.request_id) AS i
   WHERE s.is_user_process=1;
   GO

   1. Look for rows with a blocking_session_id to identify blocked sessions. Find each blocking_session_id in the list to determine if that session is also blocked. This will eventually lead you to the head blocker. Tune the head blocker query.

      [!NOTE]
      For more thorough information on troubleshooting long running or blocking queries, see Understand and resolve Azure SQL Database blocking problems.

   2. To identify a large volume of concurrent workers, review the number of requests overall and the worker_count column for each request. Worker_count is the number of workers at the time sampled and may change over time as the request is executed. Tune queries to reduce resource utilization if the cause of increased workers is concurrent queries that are running at their optimal degree of parallelism. For more information, see Query Tuning/Hinting.

2. Evaluate the maximum degree of parallelism (MAXDOP) setting for the database.

Increase worker limits

If the database or elastic pool consistently reaches its worker limit despite addressing blocking, optimizing queries, and validating your MAXDOP setting, consider scaling up the database or elastic pool to increase the worker limit.

Find resource limits for Azure SQL Database by service tier and compute size:

• Resource limits for single databases using the vCore purchasing model
• Resource limits for elastic pools using the vCore purchasing model
• Resource limits for single databases using the DTU purchasing model
• Resources limits for elastic pools using the DTU purchasing model

Learn more about Azure SQL Database resource governance of workers.

Error 10929: Resource ID: 1

10929: Resource ID: 1. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d. However, the server is currently too busy to support requests greater than %d for this database. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance. Otherwise, please try again later.

Error 40501: The service is currently busy

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: %ls. Code: %d.

This is an engine throttling error, an indication that resource limits are being exceeded.

For more information about resource limits, see Logical SQL server resource limits.

Error 40544: The database has reached its size quota

40544: The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Incident ID: <ID>. Code: <code>.

This error occurs when the database has reached its size quota.

The following steps can either help you work around the problem or provide you with more options:

1. Check the current size of the database by using the dashboard in the Azure portal.

   [!NOTE]
   To identify which tables are consuming the most space and are therefore potential candidates for cleanup, run the following SQL query:

   SELECT o.name,
    SUM(p.row_count) AS 'Row Count',
    SUM(p.reserved_page_count) * 8.0 / 1024 AS 'Table Size (MB)'
   FROM sys.objects o
   JOIN sys.dm_db_partition_stats p on p.object_id = o.object_id
   GROUP BY o.name
   ORDER BY [Table Size (MB)] DESC;
   GO

2. If the current size does not exceed the maximum size supported for your edition, you can use ALTER DATABASE to increase the MAXSIZE setting.

3. If the database is already past the maximum supported size for your edition, try one or more of the following steps:

   • Perform normal database cleanup activities. For example, clean up the unwanted data by using truncate/delete, or move data out by using SQL Server Integration Services (SSIS) or the bulk copy program (bcp) utility.
   • Partition or delete data, drop indexes, or consult the documentation for possible resolutions.
   • For database scaling, see Scale single database resources and Scale elastic pool resources.

Error 40549: Session is terminated because you have a long-running transaction

40549: Session is terminated because you have a long-running transaction. Try shortening your transaction.

If you repeatedly encounter this error, try to resolve the issue by following these steps:

1. Run the following query to see any open sessions that have a high value for the duration_ms column:

   SELECT
       r.start_time, DATEDIFF(ms,start_time, SYSDATETIME()) as duration_ms, 
       r.session_id, r.request_id, r.blocking_session_id,  
       r.status, r.command, DB_NAME(r.database_id) AS database_name,
       i.parameters, i.event_info AS input_buffer,
       r.last_wait_type, r.open_transaction_count, r.total_elapsed_time, r.cpu_time,
       r.logical_reads, r.writes, s.login_time, s.login_name, s.program_name, s.host_name
   FROM sys.dm_exec_requests as r
   JOIN sys.dm_exec_sessions as s on r.session_id=s.session_id
   OUTER APPLY sys.dm_exec_input_buffer (r.session_id,r.request_id) AS i
   WHERE s.is_user_process=1
   ORDER BY start_time ASC;
   GO

   You may choose to ignore rows where the input_buffer column shows a query reading from sys.fn_MSxe_read_event_stream: these requests are related to Extended Event sessions.

2. Review the blocking_session_id column to see if blocking is contributing to long-running transactions.

   [!NOTE]
   For more information on troubleshooting blocking in Azure SQL Database, see Understand and resolve Azure SQL Database blocking problems.

3. Consider batching your queries. For information on batching, see How to use batching to improve SQL Database application performance.

Error 40551: The session has been terminated because of excessive tempdb usage

40551: The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce the temporary table space usage.

To work around this issue, follow these steps:

1. Change the queries to reduce temporary table space usage.
2. Drop temporary objects after they’re no longer needed.
3. Truncate tables or remove unused tables.

Error 40552: The session has been terminated because of excessive transaction log space usage

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

To resolve this issue, try the following methods:

• The issue can occur because of insert, update, or delete operations.
  Try to reduce the number of rows that are operated on immediately by implementing batching or splitting into multiple smaller transactions.

• The issue can occur because of index rebuild operations. To work around this issue, make sure the number of rows that are affected in the table * (average size of field that’s updated in bytes + 80) < 2 gigabytes (GB).

  [!NOTE]
  For an index rebuild, the average size of the field that’s updated should be substituted by the average index size.

  [!NOTE]
  For more information on troubleshooting a full transaction log in Azure SQL Database and Azure SQL Managed Instance, see Troubleshooting transaction log errors with Azure SQL Database and Azure SQL Managed Instance.

Error 40553: The session has been terminated because of excessive memory usage

40553: The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.

To work around this issue, try to optimize the query.

For an in-depth troubleshooting procedure, see Is my query running fine in the cloud?.

For more information on other out of memory errors and sample queries, see Troubleshoot out of memory errors with Azure SQL Database.

Table of resource governance error messages

Error code	Severity	Description
10928	20	Resource ID: %d. The %s limit for the database is %d and has been reached. See ‘http://go.microsoft.com/fwlink/?LinkId=267637’ for assistance.. The Resource ID indicates the resource that has reached the limit. When Resource ID = 1, this indicates a worker limit has been reached. Learn more in Error 10928: Resource ID : 1. The request limit for the database is %d and has been reached. When Resource ID = 2, this indicates the session limit has been reached. Learn more about resource limits: •  Logical SQL server resource limits •  DTU-based limits for single databases •  vCore-based limits for single databases •  Azure SQL Managed Instance resource limits.
10936	20	Resource ID: %d. The %s limit for the elastic pool is %d and has been reached. See ‘http://go.microsoft.com/fwlink/?LinkId=267637’ for assistance.. The Resource ID indicates the resource that has reached the limit. When Resource ID = 1, this indicates a worker limit has been reached. Learn more in Error 10936: Resource ID : 1. The request limit for the elastic pool is %d and has been reached. When Resource ID = 2, this indicates the session limit has been reached. Learn more about resource limits: •  Logical SQL server resource limits •  DTU-based limits for elastic pools •  vCore-based limits for elastic pools •  Azure SQL Managed Instance resource limits.
10929	20	Resource ID: %d. The %s minimum guarantee is %d, maximum limit is %d, and the current usage for the database is %d. However, the server is currently too busy to support requests greater than %d for this database. The Resource ID indicates the resource that has reached the limit. For worker threads, the Resource ID = 1. For sessions, the Resource ID = 2. For more information, see: •  Logical SQL server resource limits •  DTU-based limits for single databases •  DTU-based limits for elastic pools •  vCore-based limits for single databases •  vCore-based limits for elastic pools •  Azure SQL Managed Instance resource limits. Otherwise, try again later.
40544	20	The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. For database scaling, see Scale single database resources and Scale elastic pool resources.
40549	16	Session is terminated because you have a long-running transaction. Try shortening your transaction. For information on batching, see How to use batching to improve SQL Database application performance.
40550	16	The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction. For information on batching, see How to use batching to improve SQL Database application performance.
40551	16	The session has been terminated because of excessive tempdb usage. Try modifying your query to reduce the temporary table space usage. If you are using temporary objects, conserve space in the tempdb database by dropping temporary objects after they are no longer needed by the session. For more information on tempdb limits in SQL Database, see Tempdb database in SQL Database.
40552	16	The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction. For information on batching, see How to use batching to improve SQL Database application performance. If you perform bulk inserts using the bcp.exe utility or the System.Data.SqlClient.SqlBulkCopy class, try using the -b batchsize or BatchSize options to limit the number of rows copied to the server in each transaction. If you are rebuilding an index with the ALTER INDEX statement, try using the REBUILD WITH ONLINE = ON option. For information on transaction log sizes for the vCore purchasing model, see: •  vCore-based limits for single databases •  vCore-based limits for elastic pools •  Azure SQL Managed Instance resource limits.
40553	16	The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows. Reducing the number of ORDER BY and GROUP BY operations in your Transact-SQL code reduces the memory requirements of your query. For database scaling, see Scale single database resources and Scale elastic pool resources. For more information on out of memory errors and sample queries, see Troubleshoot out of memory errors with Azure SQL Database.

Elastic pool errors

The following errors are related to creating and using elastic pools:

Error code	Severity	Description	Corrective action
1132	17	The elastic pool has reached its storage limit. The storage usage for the elastic pool cannot exceed (%d) MBs. Attempting to write data to a database when the storage limit of the elastic pool has been reached. For information on resource limits, see: •  DTU-based limits for elastic pools •  vCore-based limits for elastic pools.	Consider increasing the DTUs of and/or adding storage to the elastic pool if possible in order to increase its storage limit, reduce the storage used by individual databases within the elastic pool, or remove databases from the elastic pool. For elastic pool scaling, see Scale elastic pool resources. For more information on removing unused space from databases, see Manage file space for databases in Azure SQL Database.
10929	16	The %s minimum guarantee is %d, maximum limit is %d, and the current usage for the database is %d. However, the server is currently too busy to support requests greater than %d for this database. For information on resource limits, see: •  DTU-based limits for elastic pools •  vCore-based limits for elastic pools. Otherwise, try again later. DTU / vCore min per database; DTU / vCore max per database. The total number of concurrent workers across all databases in the elastic pool attempted to exceed the pool limit.	Consider increasing the DTUs or vCores of the elastic pool if possible in order to increase its worker limit, or remove databases from the elastic pool.
40844	16	Database ‘%ls’ on Server ‘%ls’ is a ‘%ls’ edition database in an elastic pool and cannot have a continuous copy relationship.	N/A
40857	16	Elastic pool not found for server: ‘%ls’, elastic pool name: ‘%ls’. Specified elastic pool does not exist in the specified server.	Provide a valid elastic pool name.
40858	16	Elastic pool ‘%ls’ already exists in server: ‘%ls’. Specified elastic pool already exists in the specified server.	Provide new elastic pool name.
40859	16	Elastic pool does not support service tier ‘%ls’. Specified service tier is not supported for elastic pool provisioning.	Provide the correct edition or leave service tier blank to use the default service tier.
40860	16	Elastic pool ‘%ls’ and service objective ‘%ls’ combination is invalid. Elastic pool and service tier can be specified together only if resource type is specified as ‘ElasticPool’.	Specify correct combination of elastic pool and service tier.
40861	16	The database edition ‘%.*ls’ cannot be different than the elastic pool service tier which is ‘%.*ls’. The database edition is different than the elastic pool service tier.	Do not specify a database edition that is different than the elastic pool service tier. Note that the database edition does not need to be specified.
40862	16	Elastic pool name must be specified if the elastic pool service objective is specified. Elastic pool service objective does not uniquely identify an elastic pool.	Specify the elastic pool name if using the elastic pool service objective.
40864	16	The DTUs for the elastic pool must be at least (%d) DTUs for service tier ‘%.*ls’. Attempting to set the DTUs for the elastic pool below the minimum limit.	Retry setting the DTUs for the elastic pool to at least the minimum limit.
40865	16	The DTUs for the elastic pool cannot exceed (%d) DTUs for service tier ‘%.*ls’. Attempting to set the DTUs for the elastic pool above the maximum limit.	Retry setting the DTUs for the elastic pool to no greater than the maximum limit.
40867	16	The DTU max per database must be at least (%d) for service tier ‘%.*ls’. Attempting to set the DTU max per database below the supported limit.	Consider using the elastic pool service tier that supports the desired setting.
40868	16	The DTU max per database cannot exceed (%d) for service tier ‘%.*ls’. Attempting to set the DTU max per database beyond the supported limit.	Consider using the elastic pool service tier that supports the desired setting.
40870	16	The DTU min per database cannot exceed (%d) for service tier ‘%.*ls’. Attempting to set the DTU min per database beyond the supported limit.	Consider using the elastic pool service tier that supports the desired setting.
40873	16	The number of databases (%d) and DTU min per database (%d) cannot exceed the DTUs of the elastic pool (%d). Attempting to specify DTU min for databases in the elastic pool that exceeds the DTUs of the elastic pool.	Consider increasing the DTUs of the elastic pool, or decrease the DTU min per database, or decrease the number of databases in the elastic pool.
40877	16	An elastic pool cannot be deleted unless it does not contain any databases. The elastic pool contains one or more databases and therefore cannot be deleted.	Remove databases from the elastic pool in order to delete it.
40881	16	The elastic pool ‘%.*ls’ has reached its database count limit. The database count limit for the elastic pool cannot exceed (%d) for an elastic pool with (%d) DTUs. Attempting to create or add database to elastic pool when the database count limit of the elastic pool has been reached.	Consider increasing the DTUs of the elastic pool if possible in order to increase its database limit, or remove databases from the elastic pool.
40889	16	The DTUs or storage limit for the elastic pool ‘%.*ls’ cannot be decreased since that would not provide sufficient storage space for its databases. Attempting to decrease the storage limit of the elastic pool below its storage usage.	Consider reducing the storage usage of individual databases in the elastic pool or remove databases from the pool in order to reduce its DTUs or storage limit.
40891	16	The DTU min per database (%d) cannot exceed the DTU max per database (%d). Attempting to set the DTU min per database higher than the DTU max per database.	Ensure the DTU min per databases does not exceed the DTU max per database.
TBD	16	The storage size for an individual database in an elastic pool cannot exceed the max size allowed by ‘%.*ls’ service tier elastic pool. The max size for the database exceeds the max size allowed by the elastic pool service tier.	Set the max size of the database within the limits of the max size allowed by the elastic pool service tier.

Cannot open database «master» requested by the login. The login failed

This issue occurs because the account doesn’t have permission to access the master database. But by default, SQL Server Management Studio (SSMS) tries to connect to the master database.

To resolve this issue, follow these steps:

1. On the login screen of SSMS, select Options, and then select Connection Properties.

2. In the Connect to database field, enter the user’s default database name as the default login database, and then select Connect.

   

Read-only errors

If you attempt to write to a database that is read-only, you’ll receive an error. In some scenarios, the cause of the database’s read-only status may not be immediately clear.

Error 3906: Failed to update database «DatabaseName» because the database is read-only.

When attempting to modify a read-only database, the following error will be raised.

Msg 3906, Level 16, State 2, Line 1
Failed to update database "%d" because the database is read-only.

You may be connected to a read-only replica

For both Azure SQL Database and Azure SQL Managed Instance, you may be connected to a database on a read-only replica. In this case, the following query using the DATABASEPROPERTYEX() function will return READ_ONLY:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');
GO

If you’re connecting using SQL Server Management Studio, verify if you have specified ApplicationIntent=ReadOnly in the Additional Connection Parameters tab on your connection options.

If the connection is from an application or a client using a connection string, validate if the connection string has specified ApplicationIntent=ReadOnly. Learn more in Connect to a read-only replica.

The database may be set to read-only

If you’re using Azure SQL Database, the database itself may have been set to read-only. You can verify the database’s status with the following query:

SELECT name, is_read_only
FROM sys.databases
WHERE database_id = DB_ID();

You can modify the read-only status for a database in Azure SQL Database using ALTER DATABASE Transact-SQL. You can’t currently set a database in a managed instance to read-only.

Confirm whether an error is caused by a connectivity issue

To confirm whether an error is caused by a connectivity issue, review the stack trace for frames that show calls to open a connection like the following ones (note the reference to the SqlConnection class):

System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
 at System.Data.SqlClient.SqlConnection.Open()
 at AzureConnectionTest.Program.Main(String[] args)
ClientConnectionId:<Client connection ID>

When the exception is triggered by query issues, you’ll notice a call stack that’s similar to the following (note the reference to the SqlCommand class). In this situation, tune your queries.

  at System.Data.SqlClient.SqlCommand.ExecuteReader()
  at AzureConnectionTest.Program.Main(String[] args)
  ClientConnectionId:<Client ID>

For additional guidance on fine-tuning performance, see the following resources:

• How to maintain Azure SQL indexes and statistics
• Manual tune query performance in Azure SQL Database
• Monitoring performance Azure SQL Database by using dynamic management views
• Operating the Query Store in Azure SQL Database

Steps to fix common connection issues

1. Make sure that TCP/IP is enabled as a client protocol on the application server. For more information, see Configure client protocols. On application servers where you don’t have SQL tools installed, verify that TCP/IP is enabled by running cliconfg.exe (SQL Server Client Network utility).

2. Check the application’s connection string to make sure it’s configured correctly. For example, make sure that the connection string specifies the correct port (1433) and fully qualified server name.
   See Get connection information.

3. Try increasing the connection timeout value. We recommend using a connection timeout of at least 30 seconds.

4. Test the connectivity between the application server and the Azure SQL Database by using SQL Server Management Studio (SSMS), a UDL file, ping, or telnet. For more information, see Troubleshooting connectivity issues and Diagnostics for connectivity issues.

   [!NOTE]
   As a troubleshooting step, you can also test connectivity on a different client computer.

5. As a best practice, make sure that the retry logic is in place. For more information about retry logic, see Troubleshoot transient faults and connection errors to SQL Database.

If these steps don’t resolve your problem, try to collect more data and then contact support. If your application is a cloud service, enable logging. This step returns a UTC time stamp of the failure. Additionally, SQL Database returns the tracing ID. Microsoft Customer Support Services can use this information.

For more information about how to enable logging, see Enable diagnostics logging for apps in Azure App Service.

Next steps

Learn more about related topics in the following articles:

• Azure SQL Database connectivity architecture
• Azure SQL Database and Azure Synapse Analytics network access controls
• Troubleshooting transaction log errors with Azure SQL Database and Azure SQL Managed Instance
• Troubleshoot transient connection errors in SQL Database and SQL Managed Instance
• Analyze and prevent deadlocks in Azure SQL Database