Журнал SQL Server 2008 не будет усекать

Я считаю себя очень опытным человеком SQL. Но я не могу сделать эти две вещи:

Уменьшить размер выделенного журнала.
Обрезать журнал.

DBCC sqlperf (logspace)

возвращает:

Database Name   Log Size (MB)   Log Space Used (%)  Status
ByBox       1964.25     30.0657         0

В SQL 2008 не работает

DUMP TRANSACTION ByBox WITH TRUNCATE_ONLY

Выполнение следующего ничего не делает

DBCC SHRINKFILE ('ByBox_1_Log' , 1)
DBCC shrinkdatabase(N'bybox')

Я попробовал резервное копирование. Я также попытался установить свойства базы данных "Восстановить модель" как "ПОЛНЫЙ", так и "ПРОСТОЙ" и комбинацию всех вышеперечисленных. Я также попытался установить совместимость с SQL Server 2005 (я использую этот параметр, поскольку я хочу соответствовать нашему производственному серверу) и SQL Server 2008.

Независимо от того, что я пытаюсь сделать, журнал остается на уровне 1964.25 Мб, при использовании 30%, который все еще растет.

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

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

-- Clear out pending replication stuff
exec sp_removedbreplication
go
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1
go

Попытка:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE NAME='bybox'

Возвращает

log_reuse_wait  log_reuse_wait_desc
0   NOTHING

Как я могу исправить эту проблему?

Посмотрев this и установив модель восстановления в FULL Я пробовал следующее:

USE master
GO

EXEC sp_addumpdevice 'disk', 'ByBoxData', N'C:\<path here>\bybox.bak'

-- Create a logical backup device, ByBoxLog.
EXEC sp_addumpdevice 'disk', 'ByBoxLog', N'C:\<path here>\bybox_log.bak'

-- Back up the full bybox database.
BACKUP DATABASE bybox TO ByBoxData

-- Back up the bybox log.
BACKUP LOG bybox TO ByBoxLog

который возвратил:

Processed 151800 pages for database 'bybox', file 'ByBox_Data' on file 3.
Processed 12256 pages for database 'bybox', file 'ByBox_Secondary' on file 3.
Processed 1 pages for database 'bybox', file 'ByBox_1_Log' on file 3.
BACKUP DATABASE successfully processed 164057 pages in 35.456 seconds (36.148 MB/sec).

Processed 2 pages for database 'bybox', file 'ByBox_1_Log' on file 4.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.252 MB/sec).

Perfect! Но это не так.

И DBCC SHRINKFILE ('ByBox_1_Log', 1) теперь возвращается с

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
7   2   251425  251425  251424  251424

и DBCC SQLPERF (LOGSPACE) по-прежнему сообщает о 30% использовании.

Я думаю, мне, возможно, придется смириться с тем, что в SQL Server 2008 может быть ошибка, или что файл моего журнала был поврежден каким-то образом. Однако моя база данных находится в хорошем рабочем состоянии, что заставляет меня думать, что есть ошибка (дрожь при мысли).

Ответ 1

Найденное решение!

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

Резервное копирование и вуаля, идеальный журнал 0%.

Итак, решение состоит в том, чтобы развернуть журнал.

Ответ 2

В моей ситуации у меня была 650 MB-база данных с файлом журнала 370 GB в SQL Server 2008. Независимо от того, что я пробовал, я не мог заставить его сжиматься. Я попробовал все перечисленные здесь ответы, но все равно ничего не получилось.

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

BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

Это привело к сокращению файла журнала с 37 до 1 Мб. Уф!

Ответ 3

Я нашел DBCC SHRINKFILE (Transact-SQL) (MSDN).

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

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

Ответ 4

Остерегайтесь последствий изменения моделей восстановления!

И теперь для еще одной отрезвляющей мысли для всех вас production администраторы баз данных думая об использовании script:

ПЕРЕД ВАМИ ИЗМЕНЕНИЯ МОДЕЛИ ВОССТАНОВЛЕНИЯ ОТ ПОЛНОГО К ПРОСТРАНСТВУ... нет проблем, если вы находитесь в разработке /QA. Но если вы находитесь в производственной среде, где вы несете ответственность за полное восстановление данных в случае возникновения проблемы, вы можете более внимательно изучить, что BOL говорит об этом (см. BOL в разделе "Управление базами данных" "- > " Управление журналом транзакций "- > " Модели восстановления и управление журналом транзакций "):

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

После переключения с простой модели восстановления

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

После перехода к простой модели восстановления

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

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

Рант

В Microsoft: Пожалуйста, поправьте меня, если я ошибаюсь, если я не могу сделать резервную копию t-log ПЕРЕД заменой от FULL к SIMPLE, и вот, моя база данных каким-то образом повреждена (когда-либо слышал о Закон Мерфи?), прежде чем я смогу взять резервную копию... тогда я ввернусь, не так ли? Если переключение модели восстановления моей базы данных **** производства **** из FULL в SIMPLE - это то, что может нарушить цепочку журналов резервного копирования, так что, если я не смогу сделать резервную копию журнала транзакций, прежде чем делать это (как это предлагает выше) Я потенциально потеряю данные, а затем ПОЧЕМУ, ЧТО ГОВОРЯТ, ЧТО ВЫ ВЫСШЕЕ, ЧТО В МАРКЕТЕ СМОТРЕТЬ, ИМЕЮЩЕЕ БОЛЬШУЮ ДЕЛО, чем вам кажется?! Вы должны буквально хватать меня за рубашку и хлопать меня, чтобы привлечь мое внимание (так сказать) и предупредить меня о важности этого UPFRONT!!

Ответ 5

Другим простым способом уменьшить размер файла журнала является:

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

Таким образом, вам не нужно изменять какие-либо параметры базы данных, и ваш файл журналов имеет размер 1 МБ.

Ответ 6

Я всегда ненавидел то, как SQL Server обрабатывает физическое сокращение файлов журнала. Обратите внимание, что я всегда делал это через Enterprise Manager/SQL Server Management Studio, но кажется, что когда вы сокращаете/усекаете файл журнала, физический размер файла журнала не будет уменьшаться, пока после полной резервной копии в базе данных файл данных, а затем снова создайте резервную копию файла журнала. Я никогда не смог бы сгладить точный шаблон, но вы могли бы попытаться понять, что такое точная последовательность. Тем не менее, он всегда включал полную резервную копию файла данных.

Ответ 7

Попробуйте запустить

DBCC OPENTRAN

чтобы проверить, есть ли открытые транзакции.

Ответ 8

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

При использовании SQL Server Management Studio после выполнения полной резервной копии базы данных, за которой следует полная резервная копия журнала транзакций, на странице файлов сокращений отображается много доступное свободное пространство, но оно не будет обрезать файлы журнала.

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

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

Итак, попробуйте следующее.

Сделайте полную резервную копию базы данных и файла журнала. Если вы проверите в инструменте усадки, вы увидите, что в файле журнала есть много свободного места. Однако нажатие кнопки "ОК" не приведет к удалению свободного места.
Сделайте вторую полную резервную копию базы данных и файла журнала. Вы должны найти, что полная резервная копия базы данных похожа по размеру на первую полную резервную копию. Размер полной резервной копии журнала транзакций должен быть намного меньше.
Запустите инструмент сжатия в файле журнала и, если повезет, файл журнала должен уменьшиться. В последний раз, когда я это делал, он уменьшился с 180 до 128 Мбайт, а усадочный инструмент заявляет, что в файле есть еще 10 Мб свободного места.

Ответ 9

Наконец-то я нашел решение проблемы с сокращением журнала. Все предыдущие варианты не работали для меня и не сокращали файл журнала до требуемого размера. Решение, которое я нашел, это:

Резервное копирование базы данных
Установите режим восстановления на простой
Отсоедините базу данных с помощью SQL Server Management Studio
Удалить файл журнала
Прикрепите базу данных без файла журнала. В нижней половине "добавить экран" вы получите строку, в которой говорится, что отсутствует файл журнала.
Нажмите "Удалить" для этой строки и нажмите "ОК" для прикрепления
Установите режим восстановления в полноэкранном режиме

Ответ 10

Я знаю, что вы имеете в виду - SQL-сервер может быть немного сумасшедшим. Вот пример кода для тестирования. По сути, он обрезает файл журнала, а затем пытается сжать файл. Дайте мне знать, как это работает. Еще одна вещь... у вас не было бы незавершенных транзакций, не так ли?

Use YourDatabase
GO

DBCC sqlperf(logspace)  -- Get a "before" snapshot
GO  

BACKUP LOG BSDIV12Update WITH TRUNCATE_ONLY;  -- Truncate the log file, don't keep a backup
GO

DBCC SHRINKFILE(YourDataBaseFileName_log, 2);  -- Now re-shrink (use the LOG file name as found in Properties / Files.  Note that I didn't quote mine).
GO

DBCC sqlperf(logspace)  -- Get an "after" snapshot
GO

Обновление: Саймон отмечает, что он получает ошибку в команде BACKUP. Я не понял, что "Truncate_only" был прекращен в SQL Server 2008, когда я ответил ранее. После небольшого исследования рекомендуемые шаги по сокращению файла журнала заключаются в следующем: (a) Измените модель восстановления на "Простой", а затем (b) сократите файл с помощью DBCC ShrinkFile, как указано выше. К сожалению, вы упомянули, что вы уже пытались настроить модель восстановления на Simple, поэтому я предполагаю, что впоследствии вы также запустили DBCC Shrinkfile. Это верно? Пожалуйста, дайте мне знать.

Ответ 11

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

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

Ответ 12

Читая ответы, я почти не верю, что они написаны администраторами баз данных. Основные золотые правила:

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

После этого следующие команды для сокращения журнала транзакций базы данных всегда работали нормально со мной на SQL Server 2005 и более поздних версиях SQL Server:

USE DatabaseName
GO
-- Truncate the Transaction log
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
CHECKPOINT
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO
-- Shrink the Transaction Log as recommended my Microsoft.

DBCC SHRINKFILE ('database_txlogfilelogicalname', [n -size to shrink in MBytes])
GO
 -- Pass the freed pages back to OS control.
DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY)
GO
-- Tidy up the pages after shrink
DBCC UPDATEUSAGE (0);
GO
-- IF Required but not essential
-- Force to update all tables statistics
exec sp_updatestats
GO

Ответ 13

Наконец, я пришел к выводу, что в SQLServer 2008 есть ошибка.

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

Я также запускал:

DBCC CHECKDB
DBCC UPDATEUSAGE (bybox)

И все проверено нормально.

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

Ответ 14

SQL Server 2012: У меня была проблема, когда файл журнала (и все они были уже в режиме простого восстановления) сокращался.

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

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