По материалам статьи из 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).
Ниже следует пример того, как сгенерировать фиктивные
транзакции для одного журнала с последующим усечением:
SET 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:
Q110139 INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/support/kb/articles/Q110/1/39.ASP
Q62866
INFO: Reasons Why SQL Transaction Log Is Not Being
Truncated
http://support.microsoft.com/support/kb/articles/Q62/8/66.ASP
Q66057
PRB: PRB: Running Out of Log Space When Running Large Bulk
Loads
http://support.microsoft.com/support/kb/articles/Q66/0/57.ASP
Q80629
PRB: Transaction Log Partially Truncated
http://support.microsoft.com/support/kb/articles/Q80/6/29.ASP
Разделы Books Online: Transaction Log Physical
Architecture; Optimizing Transaction Log
Performance.