Как усекается журнал транзакций SQL Server 7.0

Статьи / SQL / MS SQL Server /

Назад

По материалам статьи из Microsoft Knowledge Base

Есть несколько причин, из-за которых журнал транзакций не усекается при использовании DBCC SHRINKFILE или DBCC SHRINKDATABASE. В Books Online DBCC SHRINKFILE и DBCC SHRINKDATABASE описаны достаточно хорошо, но слишком кратко. В Микрософте SQL Server 7.0, команды SHRINKFILE и SHRINKDATABASE устанавливают желаемый размер, до которого необходимо усекать журнал. Эти команды могут быть применены для каждого журнала, но это, фактически, только заявка, которую сервер попытается выполнить. Поэтому, после выполнения команды SHRINKFILE или SHRINKDATABASE, Вам придётся дополнительно выполнить команду, которая усекает журнал и у Вас есть шанс, что это произойдёт. Вы не можете уменьшить журнал транзакций до размера меньше установленного критериями, которые представлены ниже:

- Чтобы усечь журнал транзакций до размера меньшее первоначального, Вы должны уменьшить соответствующие файлы с помощью DBCC SHRINKFILE. Вы не можете использовать DBCC SHRINKDATABASE, чтобы усечь журнал транзакций до размера меньшего его оригинала или явно заданного размера. Первоначальный размер определяется, как размер файла журнала в команде CREATE DATABASE плюс любые использованные после этого команды ALTER DATABASE. Первоначальный размер определяется без учёта автоматического роста файла журнала.

- Физический размер журнала никогда не может быть меньше используемой сервером в момент усечения его части. Вы можете использовать команду DBCC SQLPERF (LOGSPACE), чтобы увидеть количество используемого в журнале места.

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

- Поскольку журнал транзакций может быть сокращен только до границы виртуального журнала virtual log file (VLF), не возможно сократить журнал к размеру меньше чем VLF, даже если это место реально не используется. Аналогично, если часть VLF используется, Вы не можете сократить занимаемое этим VLF место. Для получения дополнительной информации, см. разделы "Virtual Log Files" и "Transaction Log Physical Architecture" в Books Online.

Журнал транзакций, это wrap-around (с запашком) журнал. Это означает, что в любое время могут существовать свободные (free) или многократно используемые (reusable) VLF, которые могут находиться в начале, в середине, и/или в конце журнала транзакций. Сократить можно только свободное место в конце журнала транзакций, а не всё свободное пространство журнала. Также, Вы можете сократить только целые VLF. Сокращаемые в конце журнала VLF должны быть неактивны. Для более детальной информации обратитесь к разделу Truncating the Transaction Log в Books Online.

Обратите внимание на следующее:

- Всегда резервируйте системные и пользовательские базы данных до и после того, как Вы вносите изменения, затрагивающие систему. DBCC SHRINKFILE и DBCC SHRINKDATABASE - не регистрируемые операции, и исполнение их нарушает последовательность отписывания транзакций из журнала в резервную копию. После выполнения этих команд, Вы должны будете сделать полное резервное копирование базы данных.

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

- Удостоверьтесь, что отсутствуют старые, долго выполняющиеся или не копируемые транзакции. Чтобы сделать это, используйте:

DBCC OPENTRAN (database_name)

- Выполните DBCC SHRINKFILE или DBCC SHRINKDATABASE, чтобы определить возможную границу усечения (shrinkpoint). DBCC SHRINKFILE и DBCC SHRINKDATABASE, по умолчанию, разрешены членам серверной роли sysadmin или роли базы данных db_owner. Для информации о различиях между этими командами, обратитесь к Books Online (обратите внимание на отличие в параметрах):

DBCC SHRINKFILE? (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)

- Создайте несколько фиктивных транзакции, чтобы имитировать журналирование транзакций и затем, выполните команду BACKUP, чтобы осуществить усечение журнала транзакций. Инструкция BACKUP фактически попытается усечь журнал транзакций к заданному целевому размеру (target_size или target_percent).

Ниже следует пример того, как сгенерировать фиктивные транзакции для одного журнала с последующим усечением:

SSET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
USE Your_Database_Name -- This is the name of the database for which the log will be
shrunk.
SELECT @LogicalFileName = 'Your_log' -- Use sp_helpfile to identify the logical file name
that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not
expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is
smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000
bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

- Проверьте, изменился ли первоначальный размер журнала транзакций.

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

После того, когда журнал транзакций усечён:

1. Выполните полное резервное копирование базы данных master.

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

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

Дополнительная информация в Microsoft Knowledge Base:

Q62866 INFO: Reasons Why SQL Transaction Log Is Not Being Truncated

Q66057 PRB: PRB: Running Out of Log Space When Running Large Bulk Loads

Q80629 PRB: Transaction Log Partially Truncated

Разделы Books Online: Transaction Log Physical Architecture; Optimizing Transaction Log Performance.