DBCC SHRINKFILE в файле журнала не уменьшает размер даже после BACKUP LOG TO DISK

Ответ 1

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

ЭТО НЕ РЕКОМЕНДУЕМАЯ ПРАКТИКА для производственных систем... Вы потеряете способность восстанавливаться в определенный момент времени из предыдущих файлов резервного копирования/журнала.

См. пример B на этой странице DBCC SHRINKFILE (Transact-SQL) msdn для примера и объяснения.

Ответ 2

Хорошо, вот решение для уменьшения физического размера файла транзакции, но без изменения режима восстановления на простой.

В вашей базе данных найдите file_id файла журнала, используя следующий запрос.

SELECT * FROM sys.database_files;

В моем случае, файл журнала - file_id 2. Теперь мы хотим найти используемые виртуальные журналы и сделать это с помощью следующей команды.

DBCC LOGINFO;

Здесь вы можете увидеть, используются ли какие-либо виртуальные журналы, посмотрев, имеет ли статус 2 (используется) или 0 (свободен). При сжатии файлов пустые виртуальные журналы физически удаляются, начиная с конца файла, пока он не достигнет первого использованного состояния. Вот почему сжатие файла журнала транзакций иногда сокращает его частично, но не удаляет все свободные виртуальные журналы.

Если вы заметили состояние 2, которое появляется после 0, это блокирует сжатие от полного сжатия файла. Чтобы обойти это, сделайте еще одну резервную копию журнала транзакций и немедленно запустите эти команды, указав указанный выше file_id и размер, на который вы хотите уменьшить размер файла журнала.

DBCC SHRINKFILE (file_id, LogSize_MB)

DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

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

Ответ 3

Я использую этот script на SQL Server 2008 R2.

USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

Ответ 4

Попробуйте это

ALTER DATABASE XXXX  SET RECOVERY SIMPLE

use XXXX

declare @log_File_Name varchar(200) 

select @log_File_Name  = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50) 

Ответ 5

Пол Рэндал имеет замечательное обсуждение этой проблемы в своем блоге: http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

Ответ 6

Сокращение файла журнала

Для файлов журнала, Database Engine использует target_size для вычисления целевого размера для всего журнала; поэтому target_size - это количество свободного места в журнале после операции усадки. Размер цели для всего журнала затем преобразуется в целевой размер для каждого файла журнала. DBCC SHRINKFILE пытается сжать каждый физический файл журнала до его целевого размера немедленно.

Однако, если часть логического журнала находится в виртуальных журналах за пределами целевого размера, Database Engine освобождает как можно больше места, а затем выдает информационное сообщение.

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

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

• Устранение неполадок: файл не сжимается

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

Выполните следующий запрос.

SELECT name, size/128.0 - CAST (FILEPROPERTY (имя, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

Запустите команду DBCC SQLPERF, чтобы вернуть пространство, используемое в журнале транзакций.

• Если недостаточно свободного места, операция сжатия не может уменьшить размер файла.

• Обычно это файл журнала, который не сжимается. Обычно это результат файла журнала, который не был усечен.

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

Пример:

Сокращение файла журнала до указанного целевого размера

Следующий пример сокращает файл журнала в базе данных AdventureWorks до 1 МБ. Чтобы команда DBCC SHRINKFILE сжимала файл, файл сначала усекается, устанавливая модель восстановления базы данных в SIMPLE.

Transact-SQL

ИСПОЛЬЗОВАТЬ AdventureWorks2012;
GO
- Усечь журнал, изменив модель восстановления базы данных на SIMPLE.
ALTER DATABASE AdventureWorks2012
УСТАНОВИТЬ ВОССТАНОВЛЕНИЕ ПРОСТОЙ | GO
- Сжатие усеченного файла журнала до 1 МБ.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
- Reset модель восстановления базы данных.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL,
GO

Когда вы используете DBCC SHRINKFILE (Logfile, size), он только усекает с конца файла журнала, насколько это возможно. Когда он достигает наивысшего виртуального журнала, который все еще используется, он не может сокращаться. Это описано в электронной документации по SQL Server по адресу:

http://technet.microsoft.com/en-us/library/ms189493.aspx

Итак, как только верхний конец журнала станет ясным, его можно уменьшить по размеру. Опять же, это будет зависеть от того, какая часть журнала все еще используется. Журнал может быть очищен резервными копиями, но резервные копии не очистят неполные транзакции, поэтому журнал может оставаться в высокопроизводительном VLF даже после повторных резервных копий.

Что касается увеличения и уменьшения VLF, насколько величен был файл журнала, который был первоначально создан и каков параметр для роста файла журнала? Если он вырастет всего на небольшую сумму, он создаст больше VLF, чем кто-либо желает.

Общим шаблоном для сокращения файла журнала является CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE и т.д., пока вы не получите результаты. Существует много причин, по которым журнал не может быть усадочным, включая очень большой откат.

Переключение с Simple на Full имеет проблему:

Здесь есть правила и исключения. Мы поговорим о длительных транзакциях по глубине ниже.

Но одно предостережение в отношении режима полного восстановления: если вы просто переключаетесь в режим полного восстановления, но никогда не берете начальное полное резервное копирование, SQL Server не будет соблюдать ваш запрос в модели Full Recovery. Ваш журнал транзакций будет продолжать работать так же, как и в Simpleuntil, когда вы переключаетесь на полную модель восстановления и выполняете первое полное резервное копирование.

Полная модель восстановления без резервных копий журнала плохая:

Итак, что является наиболее распространенной причиной неконтролируемого роста журнала? Ответ: Быть в режиме полного восстановления без каких-либо резервных копий журнала.

Это происходит постоянно для людей.

Почему такая распространенная ошибка?

Почему это происходит все время? Поскольку каждая новая база данных получает первоначальную настройку модели восстановления, просматривая базу данных модели.

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

Ответ 7

Я пробовал много способов, но это работает.

Пример кода доступен в DBCC SHRINKFILE

USE DBName;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE DBName  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (DBName_log, 1);  --File name SELECT * FROM sys.database_files; query to get the file name
GO  
-- Reset the database recovery model.  
ALTER DATABASE DBName  
SET RECOVERY FULL;  
GO

Ответ 8

Благодаря @user2630576 и @Ed.S.

следующее обработано:

BACKUP LOG [database] TO DISK = 'D:\database.bak'
GO

ALTER DATABASE [database] SET RECOVERY SIMPLE

use [database]

declare @log_File_Name varchar(200)

select @log_File_Name = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50)

ALTER DATABASE [database] SET RECOVERY FULL