Sql Server: удаление фрагментов по-прежнему заполняет журнал транзакций; при неудаче все удаления откатываются - почему?

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

У нас есть задание, которое использует данные из sp_spaceused, чтобы определить, достигли ли мы определенного порогового размера. Если порог превышен, мы определяем, сколько строк необходимо удалить, чтобы уменьшить размер до x процентов от этого порога. (Кстати, я использую exec sp_spaceused MyLogTable, TRUE, чтобы получить количество строк и приблизительное приближение к их среднему размеру, хотя я не уверен, что это лучший способ сделать это. Но это уже другая проблема. )

Затем я пытаюсь удалить фрагменты (скажем, 5000 за раз), зациклив вызов sproc, который в основном делает следующее:

DELETE TOP (@RowsToDelete) FROM [dbo].[MyLogTable]

пока я не удалю то, что нужно удалить.

Вот проблема: если мне нужно удалить много строк, файл журнала транзакций заполняется. Я могу смотреть, как он растет, бегая

dbcc sqlperf (logspace)

Что меня озадачивает, так это то, что при сбое задания ВСЕ удаленные строки откатываются. Другими словами, похоже, что все фрагменты (каким-то образом) обертываются неявной транзакцией.

Я пробовал явно отключить неявные транзакции, заключив каждый оператор DELETE в BEGIN и COMMIT TRAN, но безрезультатно: либо все удаленные фрагменты выполняются успешно, либо вообще не выполняются.

Я знаю, что простой ответ: сделайте свой файл журнала достаточно большим, чтобы обрабатывать максимально возможное количество записей, которые вы когда-либо удаляете, но все же, почему это рассматривается как одна транзакция?

Извините, если я пропустил что-то простое, но я просмотрел много сообщений о росте файла журнала, режимах восстановления и т. Д., И я не могу этого понять.

Еще одна вещь: после сбоя задания файл журнала остается заполненным на 95–100 процентов в течение некоторого времени, прежде чем он снова упадет. Однако, если я убегу

checkpoint
dbcc dropcleanbuffers

он сразу же снижается до уровня использования примерно до 5 процентов.

TIA.


sql-server tsql transactions implicit transaction-log
person Paul K    schedule 30.04.2010    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Файл журнала в простой модели восстановления автоматически усекается для каждой контрольной точки, вообще говоря. Вы можете вызывать контрольную точку вручную, как в конце цикла, но вы также можете делать это на каждой итерации. Частота контрольных точек по умолчанию автоматически определяется сервером sql на основе настройки интервала восстановления.

Что касается «все удаления отменены», я не вижу другого объяснения, кроме внешней транзакции. Можете ли вы опубликовать весь код, очищающий журнал? Как вы вызываете этот код? Какая у вас настройка неявных транзакций?

Хм ... если журнал растет и не обрезается автоматически, это также может указывать на то, что транзакция выполняется вне цикла. Можете ли вы select @@trancount перед циклом и, возможно, на каждой итерации узнать, что происходит?

person Piotr Rodak    schedule 30.04.2010
comment
Петр, спасибо за предложения. Фактически, я выполнял sproc драйвера из Query Analyzer. Ваш вопрос о внешней транзакции заставил меня задуматься: устанавливает ли Query Analyzer по умолчанию implicit_transactions? Я проверил Profilier и не увидел этого. Однако я попробую еще раз с SET IMPLICIT_TRANSACTIONS OFF в моем сеансе Query Analyzer и посмотрю, имеет ли это значение (все или некоторые откатываются при неудаче). Я сделаю репост, когда проведу этот тест. Спасибо, Пол - person Paul K; 30.04.2010

arrow_upward
0
arrow_downward

Ну, я пробовал несколько вещей, но все равно все удаления откатываются. Я добавил printint @@TRANCOUNT как до, так и после удаления и получил ноль в качестве счетчика. Тем не менее, в случае сбоя все удаления откатываются ... Я добавил SET IMPLICIT_TRANSACTIONS OFF в нескольких местах (в том числе в моем первоначальном вызове из Query Analyzer, но это, похоже, не помогает. Это тело вызываемой хранимой процедуры (Я установил @RowsToDelete на 5000 и 8000):

  SET NOCOUNT ON;
  print N'@@TRANCOUNT PRIOR TO DELETE: ' + CAST(@@TRANCOUNT AS VARCHAR(20));

  set implicit_transactions off;
  WITH RemoveRows AS 
  (
    SELECT ROW_NUMBER() OVER(ORDER BY [Date] ASC) AS RowNum
    FROM [dbo].[Log4Net]
  )

  DELETE FROM RemoveRows
  WHERE RowNum < @RowsToDelete + 1

  print N'@@TRANCOUNT AFTER DELETE: ' + CAST(@@TRANCOUNT AS VARCHAR(20));

Он вызывается из этого t-sql:

WHILE @RowsDeleted < @RowsToDelete
BEGIN
 EXEC [dbo].[DeleteFromLog4Net] @RowsToDelete 
 SET @RowsDeleted = @RowsDeleted + @RowsToDelete
 Set @loops = @loops + 1
 print 'Loop: ' + cast(@loops as varchar(10))
END

Признаюсь, я озадачен. Я не гуру БД, но я думал, что понял достаточно, чтобы понять это ....

person Paul K    schedule 03.05.2010
comment
Что ж, мне очень неприятно это признавать, но в @RowsToDelete я проходил через общее количество удаляемых строк, а не размер моего блока. Вздох. Еще несколько заявлений для печати и еще одна пара глаз поймали мою ошибку .... Проблема решена. - person Paul K; 03.05.2010