Dbcc shrinkfile не уменьшает файл

Обновлено: 06.07.2024

Для MS SQL 2008/2012 рекомендации ИТС уже устарели, кроме того и раньше они не всегда помогали. В статье попытался собрать наиболее полный комплект информации по данному вопросу.
В своё время в одном месте всего этого не нашел, поэтому думаю будет полезно.

Собственно там рекомендуется следующий скрипт:

BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY

DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)

Если выполнить его в MS SQL 2008/2012 получите ошибку:

'truncate_only' is not a recognized BACKUP option

Что теперь делать?
Решения, собственно два:

1)
USE [Database]
ALTER DATABASE [Database] SET RECOVERY SIMPLE
go
DBCC SHRINKFILE ([Database]_log, 1);
ALTER DATABASE [Database] SET RECOVERY FULL
go

2)
USE [Database]
BACKUP LOG [Database] TO DISK='NUL:'
go
DBCC SHRINKFILE ([Database]_log, 1)
go

Если "Урезанием лога" не злоупотреблять (т.е. сокрашать лог вместе с полной копией) то по большому счету не принципиально каким методом пользоваться.
Второй вроде как правильнее, зато первый "надежнее".

На этом казалось бы можно и остановиться, но зачем тогда отдельную статью писать. Нет, конечно это ещё не всё. Обычно вопросы про урезание лога возникают когда это сделать не получается.
Притом способы, описанные выше, как правило, описаны не раз, все их освоили и проблем не вызывают.

Итак, если все действия, описанные выше не помогли - лог файл по-прежнему занимает N гигабайт. Переходим к плану B:

select log_reuse_wait_desc from sys.databases

В результате можете получить 3 варианта:

а. Пусто - Обычно это означает что у БД лог можно хоть сейчас полностью сократить, могу предложить только попробовать ещё раз Shrink, а если не поможет - переходить к плану C
b. Log_Backup - Нормальный варинат. В данном случае говорит о том, что Backup Log не выполнено, или выполнено некорректно
b. Replication - значит что ваш лог не обрезается из за репликации - скорее всего ошибки.
с. Active transactions - Самая частая ситуация - в базе есть подвисшие транзакции, с ними нужно разобраться.

Replication - Репликация для систем на платформе 1С, пожалуй, бессмысленное дело. Потому как Read only баз MS SQL не бывает, средства создания распределенных систем в 1С есть собственне (да, я про РИБ). Для обеспечения отказоустойчивости гораздо лучше подходят кластерные технологии. Собственно рекоммендация простая:

sp_removedbreplication '[Database]'

Собственно после этого бэкап и Shrink помогут. Если же вопреки здравому смыслу вы всё-таки хотите сохранить репликацию БД то конечно выполнять эту команду нельзя, а нужно разбираться с ошибками репликации. Но это уже тема отдельной статьи.

Active transactions - наиболее популярная история. В базе есть транзакции, которые не завершены, и чего то ожидают. Чащи всего такие транзакции получаются при потере сетевого соединения или "вылете" клиента 1С в момент записи в БД. В этом случае нужно собственно узнать какая транзакция "повисла":

DBCC OPENTRAN

После выполнения этой команды вы получите примерно следующий результат:

Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : May 5 2014 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Из этого обилия информации ключевым является Start Time и SPID. Если транзакция в базе 1С выполняется боле нескольких секунд это уже означает что что-то не так. А если start Time будет минут 10 или более от текущего времени - такие транзакции (сеансы) нужно завершать. Но предварительно я бы рекоммендовал узнать что эта транзакция делала.

Для завершения процесса можно ввести команду

KILL [Process ID]

Где Process ID - это тот самый SPID полученный на предыдущем шаге. При этом незавершенные транзакции откатятся средствами MS SQL Server. Возможно при "убийстве" процесса будут завершены и несколько сеансов 1С, но вряд ли много. Сервер 1С поддерживает собственный пул соединений с MS SQL, соответственно соединения из этого пула используются только тогда, когда серверу что-то нужно от СУБД. При этом если соединение занято (а оно как видим занято) вряд ли оно будет использоваться для других процессов.

Но предварительно (!) если хотите всё-таки разобраться в проблеме рекомендую выполнить скрипт вроде:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [Process ID]
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

В результате вы получите текст команды SQL Server, на которой, собственно, всё и "зависло". Из неё вам нужна будет таблица в которую производилась запись, далее используя функцию "ПолучитьСтруктуруХраненияБазыДанных()" вы определите таблицу в терминах объектов метаданных в которую производилась запись и смотрите код. Как правило такие неприятные последствия происходят:

1) Ошибки в сетевых подключениях (для толстого клиента в т.ч. в сетевых подключениях клиентов, для тонкого - только в проблемах сети между сервером 1С и MS SQL).
2) Каких то неправильных действиях (отправка почты, запись в файл, запуск внешних обработок, чтения из файла) производимых в транзакциях (при записи, при проведении)

Собственно от них надо избавляться.

Если ничего не помогло (или план B)

ВНИМАНИЕ! Перед выполнением процедур, описанных ниже, сделать полную резервную копию файлов БД MS SQL нужно обязательно.

Есть ещё один - более радикальный способ решения вопроса роста журнала транзакций MS SQL. Но я лично его бы не рекомендовал к использованию. Тем не менее, специалисты Microsoft тоже могут ошибаться,
и SQL Server может содержать ошибки, о которых мы регулярно читаем в BugFix, или же наблюдаем сами, поэтому приведу и этот способ.

Суть его заключается в том что журнал транзакций просто удаляется и создается новый. При этом вы конечно теряете информацию из него и БД можно будет восстановить только из полной копии (которую вы конечно перед этим сделали).

Конечно при этом, особенно если в базе были всё-таки не зафиксированные может быть нарушена логическая целостность, но для этого запускается CheckDB которая в общем и целом приводит базу в порядок. Для аналогии это то же самое что в 1С проврять ссылочную целостность с опцией "Удалять если не найден". Если транзакция полностью не зафиксирована, но от неё остались частично данные, что противоречит принципу атомарности транзакций - эти данные будут удалены.

1) Detach БД из списка

2) Фал *.ldf удаляем (вы же его сохранили уж, да?)

3) Файл *.mdf переименовываем (в любое имя какое нравится)

4) В MS SQL создаём новую (. ) БД с тем же именем, с каким была "больная" БД

5) Останавливаем MS SQL Server

6) Новый *.mdf файл удаляем, а старый переименовываем под "старое имя", подменяя тем самым файл новой БД

7) Запускаем MS SQL Server. При этом будет "битая БД", далее мы её исправляем

8) ALTER DATABASE [Database] SET EMERGENCY

9) exec sp_dboption [Database], 'single user', 'TRUE'
Монопольный режим работы с БД

10) DBCC CHECKDB ([Database], REPAIRALLOWDATA_LOSS)
Ключевая операция - "возвращает БД к жизни". Может выполняться достаточно долго - до получаса на больших БД. Ни в коем случае не прерывайте эту операцию. Результат, где будут собраны исправленные ошибки
на всякий случай сохраните

11) exec sp_dboption [Database], 'single user', 'FALSE'
Сбрасываем монопольный режим

12) ALTER DATABASE [Database] SET ONLINE
Делаем базу доступной.

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

Я хотел сжать файл журнала как можно больше, и поэтому я начал свой поиск, чтобы выяснить, как это сделать. Предостережение: я не DBA (или даже приближаюсь к DBA) и продвигаюсь на ощупь через этот квест.

во-первых, я просто вошел в SSMS, свойства БД, файлы и отредактировал начальное значение размера (МБ) до 10. Что уменьшил файл журнала до 62 ГБ (не совсем 10 МБ, которые я ввел). Итак, я прикрепил SQL Profiler, увидел, что вызывается DBCC SHRINKFILE. Затем я ввел эту команду в Редактор запросов, и вот результаты.

затем я провел некоторое исследование и обнаружил следующее:

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

Итак, я решил, что попытаюсь создать резервную копию файла журнала, а затем сделать DBCC SHRINKFILE (и я изменил новый размер файла журнала на 12800, так как это был минимальный размер, указанный в выводе предыдущей команды DBCC SHRINKFILE)

результат был таким же, как и первый обход. Я могу получить только файл журнала до 62 ГБ.

Я не уверен, что я делаю неправильно и что я должен попробовать следующий.

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

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

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

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

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

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

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

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

инструкция DBCC shrinkfile команда (столбцом file_id, LogSize_MB)

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

Я использую этот скрипт на sql server 2008 R2.

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

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

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

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

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

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

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

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

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

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

пример :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сначала я просто зашел в SSMS, свойства БД, файлы и отредактировал значение начального размера (МБ) до 10. Это уменьшило размер файла журнала до 62 ГБ (не совсем те 10 МБ, которые я ввел). Итак, я прикрепил SQL Profiler и увидел, что вызывается DBCC SHRINKFILE. Затем я ввел эту команду в редактор запросов, и вот результаты.

И результат был:

Затем я провел небольшое исследование и обнаружил следующее:

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

Итак, я подумал, что попытаюсь сделать резервную копию файла журнала, а затем выполнить DBCC SHRINKFILE (и я изменил размер нового файла журнала на 12800, поскольку это был MinimumSize, указанный в выходных данных предыдущей команды DBCC SHRINKFILE)

Результат был таким же, как и при первом обходе. Я могу уменьшить размер файла журнала только до 62 ГБ.

Я не уверен, что делаю не так и что мне делать дальше.

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

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

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

Спасибо @ user2630576 и @ Ed.S.

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

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

Мы делаем SHRINKFILE во время уборки после установки ВОССТАНОВЛЕНИЯ на ПРОСТОЙ. Мы делаем полную резервную копию в начале и в конце процесса, чтобы преодолеть проблему потери возможности восстановления на определенный момент времени из предыдущих резервных копий / файлов журналов. Мы используем некоторый флаг в базе данных, чтобы гарантировать, что дифференциальное резервное копирование не будет выполнено, пока служебное задание не будет завершено. Краткое изложение приведено ниже - Работа по дому:

Установите статус «Выполняется уборка».
Установите базу данных в однопользовательский режим
Сделайте полную резервную копию базы данных
Удалять старые записи из различных таблиц
Установите режим ВОССТАНОВЛЕНИЯ базы данных на ПРОСТОЙ
Перебирайте файлы журнала и сжимайте каждый из них.
Установите режим ВОССТАНОВЛЕНИЯ базы данных на ПОЛНЫЙ
Сделайте полную резервную копию базы данных
Установите базу данных в многопользовательский режим
Установите статус "Уборка завершена".

Задание дифференциального резервного копирования:

Продолжайте, только если статус - «Уборка завершена».
Сделайте дифференциальную резервную копию

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

Я решил эту проблему, сделав полную и транзакционную резервную копию. Иногда процесс резервного копирования не завершается, и это одна из причин, по которой файл .ldf не сжимается. Попробуй это. У меня это сработало.

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

Образец кода доступен в DBCC SHRINKFILE

Я использую этот скрипт на sql server 2008 R2.

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

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

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

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

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

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

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

В следующей таблице отображены столбцы результирующего набора.

Инструкция DBCC SHRINKFILE применяется к файлам в текущей базе данных. Дополнительные сведения об изменении текущей базы данных см. в разделе USE (Transact-SQL).

Операции DBCC SHRINKFILE могут быть остановлены на любом этапе процесса, при этом вся выполненная работа сохраняется.

В случае сбоя операции DBCC SHRINKFILE возникает ошибка.

Сжимаемая база данных необязательно должна находиться в однопользовательском режиме; при выполнении сжатия файла в базе данных могут работать другие пользователи. Для сжатия системных баз данных также не обязательно запускать экземпляр SQL Server в однопользовательском режиме.

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

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

Устранение неполадок

Этот раздел описывает методы диагностики и устранения проблем, которые могут произойти при выполнении команды DBCC SHRINKFILE:

Файл не удалось сжать

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

Выполните следующий запрос.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;
Выполните команду DBCC SQLPERF, чтобы освободить пространство, используемое журналом транзакций.

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

Обычно это файл журнала, который сжимается незначительно. Это характерно для файла журнала, который не был усечен. Можно усечь файл журнала, установив значение SIMPLE для модели восстановления базы данных или создав резервную копию журнала, а затем выполнив операцию DBCC SHRINKFILE снова.

Операция сжатия заблокирована

Читайте также:

Dbcc shrinkfile не уменьшает файл

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

Рекомендации

Устранение неполадок

Файл не удалось сжать

Операция сжатия заблокирована