DBCC SHRINKFILE в файле журнала не уменьшает размер даже после резервного копирования журнала на диск

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

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

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;

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

Я использую этот скрипт на 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

попробуй такое

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) 

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

сжатие файла журнала

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

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

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

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

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

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

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

выберите имя, размер/128.0-CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 как AvailableSpaceInMB от sys.database_files;

запустить DBCC SQLPERF возвратить пространства в журнале транзакций.

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

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

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

пример :

сжатие файла журнала до заданного целевого размера

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

Transact-SQL

использовать Данных adventureworks2012;
Иди
-- Усечение журнала путем изменения модели восстановления базы данных на простой.
ALTER DATABASE AdventureWorks2012
УСТАНОВИТЬ ВОССТАНОВЛЕНИЕ ПРОСТО;
Иди
-- Сжать усеченный файл журнала до 1 МБ.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
Иди
-- Сброс модели восстановления базы данных.
ALTER DATABASE AdventureWorks2012
УСТАНОВИТЬ ПОЛНОЕ ВОССТАНОВЛЕНИЕ;
Иди

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

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

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

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

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

переключение с простого на полный имеет проблему:

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

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

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

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

это происходит все время люди.

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

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

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

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

пример кода доступен в 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

благодаря @user2630576 и @Ed.С.

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

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