То, как спасти запрос SQL-сервера 2008 от, УНИЧТОЖАЛО/ОТКАТЫВАЛО состояние?
У меня есть хранимая процедура, которая вставляет пакеты миллионов строк, появляющихся из определенного запроса, в базу данных SQL. Это имеет один параметр, выбирающий пакет; когда этот параметр будет опущен, он соберет список пакетов и рекурсивно назовет себя для итерации по пакетам. В (псевдо-) код, это выглядит примерно так:
CREATE PROCEDURE spProcedure AS BEGIN
IF @code = 0 BEGIN
...
WHILE @@Fetch_Status=0 BEGIN
EXEC spProcedure @code
FETCH NEXT ... INTO @code
END
END
ELSE BEGIN
-- Disable indexes
...
INSERT INTO table
SELECT (...)
-- Enable indexes
...
Теперь это может произойти, что эта процедура является медленной по любой причине: это не может получить блокировку, один из индексов, которые это использует, является misdefined или отключенный. В этом случае я хочу смочь, уничтожают процедуру, усекают и воссоздают получающуюся таблицу и попробовали еще раз. Однако то, когда я пытаюсь уничтожить процедуру, процесс часто медленно сочится в, УНИЧТОЖАЛО/ОТКАТЫВАЛО состояние, из которого там, кажется, не возврат. От Google я учился делать sp_lock, найдите spid и затем уничтожьте его с KILL . Но когда я пытаюсь уничтожить его, это говорит мне
SPID 75: происходящий откат транзакции. Предполагаемое завершение отката: 0%. Предполагаемое время, оставаясь: 554 секунды.
Я действительно находил сообщение форума, подсказывающее, что другой spid должен быть уничтожен, прежде чем другой может запустить откат. Но это не работало на меня также, плюс я не понимаю, почему это имело бы место..., это могло быть, потому что я рекурсивно называю свою собственную хранимую процедуру? (Но это должно иметь тот же spid, правильно?)
В любом случае мой процесс просто находится там, будучи мертвым, не отвечая на уничтожения, и блокируя таблицу. Это очень печально, поскольку я хочу продолжить разрабатывать свои запросы, часы не ожидания на моем сервере, находящемся мертвый, симулируя заканчивать воображаемый откат.
Есть ли некоторый путь, которым я могу сказать серверу не хранить информацию отката для своего запроса? Или не позволить каким-либо другим запросам вмешиваться в откат, так, чтобы это не занимало много времени? Или как переписать мой запрос лучшим способом, или как уничтожают процесс успешно, не перезапуская сервер?
5 ответов
Несколько комментариев.
Во-первых, gbn верен в отношении невозможности отменить выполняющийся откат. Таким образом SQL поддерживает целостность транзакций, и вы не захотите изменить это поведение. Если вам абсолютно все равно, и вы просто хотите вернуть свою БД туда, где вы были, когда была сделана последняя резервная копия, следуйте его шагам.
Однако есть одно предостережение. Бывали случаи, когда spid на самом деле не откатывается , а просто зависает (обычно при 0% или 100% прогрессе). Самый надежный индикатор в этом случае - если счетчики CPU / IO для spid в мониторе активности не меняются (и SPID не блокируется другим SPID). В этом случае вам может потребоваться перезапустить службу SQL (не нужно выполнять полную перезагрузку), чтобы очистить spid.
Что касается реорганизации вашего запроса, чтобы эти откаты не наносили вам вреда, то да, это возможно. Просто используйте явные транзакции:
WHILE @@Fetch_Status=0 BEGIN
BEGIN TRANS
EXEC spProcedure @code
COMMIT TRANS
FETCH NEXT ... INTO @code
END
Данные фиксируются после каждого пакета. Если у вас возникла проблема и вам нужно убить spid, он должен откатить только текущий пакет, над которым он работает.
Если даже одного пакета слишком много, вы, вероятно, могли бы провести рефакторинг "spProcedure", чтобы вставлять меньшие пакеты по 10–100 тысяч записей с фиксацией после каждого из них.
Откат транзакции.
Это будет продолжаться, даже если вы перезапустите экземпляр.
Если вы вставили или удалили 99 миллионов строк при вставке или удалении 100 миллионов, все 99 миллионов нужно было откатить. Вы не можете изменить это поведение. Любой отдельный оператор DML является атомарным.
Если вы хотите исправить это:
- Остановить SQL Server
- Удалить файлы БД
- Запустить SQL Server
- УДАЛИТЬ БД в нерабочем состоянии
- Восстановить
YMMV, конечно: -)
Когда вы завершаете процесс SQL Server, он не умирает немедленно, всю работу, выполняемую активной транзакцией этого процесса, необходимо сначала откатить. Откат может занять значительное время - возможно, столько же или даже больше, чем запрос использовал при выполнении до уничтожения.
Я также видел ошибку, при которой прекращенный / откатный процесс остается неопределенным. К счастью, в том случае, когда я это видел, этот процесс не удерживал никаких значительных блокировок!
Есть шаги, которые вы можете предпринять, чтобы избежать этого, но я не хочу рекомендовать что-либо подобное, не понимая ваших требований и ситуации, поскольку это может отрицательно повлиять на другие процессы / запросы.
Был здесь. В прошлый раз у нас было обработано около 3,2 миллиарда записей. Первоначальный отчет был выполнен на 99% в течение десяти минут; затем потратил 20 часов на ввод-вывод.
Первый раз он работал около 8 часов, затем автоматическое резервное копирование остановило процесс и отключило сервер. На то, чтобы снова запустить его, потребовалось почти 2 дня, чтобы мы могли начать процесс заново ... На этот раз убедившись, что процесс резервного копирования отключен.
Насколько я понимаю, не без потери целостности файла данных путем выполнения чего-то неприятного, например, жесткого сброса, а затем SQL перейдет в состояние восстановления и по-прежнему будет выполнять некоторые аспекты отката, чтобы убедиться, что откат транзакций успешно откатился.
Если что-то вроде nolock не поможет вам преодолеть существующую блокировку (вы не упомянули, является ли ее исключительная блокировка) - вы, вероятно, все еще можете написать скрипт для схемы таблицы, сделать ее MyTable2 - а затем продолжать писать запросы и вернуться и измените их, когда закончите.