Как сделать шринк лога ms sql
Добавил пользователь Дмитрий К. Обновлено: 05.10.2024
Как бы много нам не было известно - мы всё равно всегда готовы изучать все новое. В этой статье я покажу вам, как выполнить сжатие временной базы данных TempDB без остановки работающего экземпляра MS SQL Server, не зная ни количества файлов, ни их имена в БД TempDB.
Давайте разберемся что же это? И зачем так необходимо? При работе 1С:Предприятия в клиент-серверном варианте интенсивно используются временные таблицы. Эти таблицы хранятся в системной базе данных TempDB. В процессе работы временная база данных может значительно увеличиваться в размерах и привести к заполнению всего диска.
Причиной увеличения размера базы данных TempDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства из-за наличия активных транзакций, использующих объекты этой базы данных. Неограниченный рост может вызвать проблемы, связанные с сервером, вплоть до остановки MS SQL Server. В результате - недоступность баз, что в свою очередь вызовет остановку в работе всех пользователей 1С использующих базы на этом сервере.
Чтобы не допустить ничего подобного, следует выполнить основные действия по уменьшению размера базы данных TempDB до требуемой величины:
1. Перезапустить MS SQL Server. В этом случае размер базы данных TempDB будет установлен по умолчанию.
2. Сжать базу данных TempDB.
3. Уменьшить размер отдельных файлов.
4. Переместить базу данных TempDB на диск большего размера.
Решил я эту задачу путем уменьшения размера отдельных файлов.
Но для начала немного вводной информации. Временная база данных является системной базой данных, которая используется как для системных, так и для прикладных задач. Вот часть из них:
Также TempDB хранит много временной (кешированной) информации, которая используется для ускорения запросов.
Если место в TempDB уже освободилось, то для освобождения места на диске можно выполнить ее сжатие (shrink). Сделать это можно в SSMS студии:
Здесь вы можете убедиться, что сжатие tempdb не похоже на сжатие любой другой базы данных. Вероятней всего операция shrink не привела к уменьшению файла БД, значит необходимо очистить различные кэш сервера и повторить shrink.
Важно. Все перечисленные ниже операции удаляют все виды кеш, что влияет на производительность сервера, пока они не будут восстановлены SQL Server. Помимо этого все будет удалено из буферов и записано на диск. Это доп. нагрузка на подсистему ввода/вывода. После этого можно сжать файлы, что влияет на производительность чтения/записи. И наконец, все процессы, которые запрашивают данные, должны будут извлечь данные из подсистемы ввода/вывода в буферы. Это значительно влияет на производительность системы в целом. Не выполняйте операции без крайней необходимости!
Вначале хотелось бы напомнить про самый простой способ. Если не используется производственная среда (например, среда разработки), то лучше всего перезапустить службу SQL Server. Это вернет tempdb к его размеру по умолчанию. Но если нет возможности перезапуска службы? В таком случае необходимо сжать TempDB без остановки MS SQL. Для этого используются следующие команды T-SQL:
| Команда | Описание | Инструкция для SSMS |
|---|---|---|
| DBCC DROPCLEANBUFFERS | Создаем checkpoint, чтобы сбросить на диск кешированные индексы и буферы страницы данных | CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO |
| DBCC FREEPROCCACHE | Чистим кеш хранимых процедур. Это означает, что некоторые запросы и хранимые процедуры придется перекомпилировать при следующем их запуске. Хотя компиляция происходит автоматически, можно заметить значительное снижение производительности в первые несколько запусков запросов и процедур | DBCC FREEPROCCACHE; GO |
| DBCC FREESYSTEMCACHE | Эта операция аналогична FREEPROCCACHE, за исключением того, что она влияет на другие типы кеш. | DBCC FREESYSTEMCACHE ('ALL'); GO |
| DBCC FREESESSIONCACHE | Чистим кеш сессий. Это связано с распределенными запросами (запросами между серверами) | DBCC FREESESSIONCACHE; GO |
| DBCC SHRINKFILE | Это тот же инструмент, который используется для сжатия любого файла базы данных в TempDB или других базах данных. Это шаг, который фактически освобождает нераспределенное пространство из файла базы данных. Этот шаг самый “хитрый”. Во время процесса сжатия никакое другое действие не должно использовать базу данных tempdb (например бекапирование). Т.е. не должно быть открытых транзакций. Открытые транзакции могут привести к сбою операции DBCC | DBCC SHRINKFILE (TEMPDEV, 1024); Новый размер файла в МБ GO |
На практике TempDB состоит из нескольких файлов. На различных серверах их название и кол-во отличаются см. Рис1 и Рис2.
Рисунок 1
Рисунок 2
Как же выполнить DBCC SHRINKFILE заранее, не зная кол-ва файлов и их имена в TempDB?
Проще всего обратится не к имени файла, а к его порядковому номеру в базе. Это демонстрирует листинг кода скрипта. Скрипт формирует текст T-SQL для выполнения из командной строки, в результате выполнения которого получаются команды вида:
CHECKPOINT DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS DBCC FREEPROCCACHE WITH NO_INFOMSGS DBCC FREESYSTEMCACHE ('ALL') WITH NO_INFOMSGS DBCC FREESESSIONCACHE WITH NO_INFOMSGS DBCC SHRINKDATABASE ([tempdb]) WITH NO_INFOMSGS DBCC SHRINKFILE (1,64) WITH NO_INFOMSGS DBCC SHRINKFILE (2,64) WITH NO_INFOMSGS DBCC SHRINKFILE (3,64) WITH NO_INFOMSGS DBCC SHRINKFILE (4,64) WITH NO_INFOMSGS DBCC SHRINKFILE (5,64) WITH NO_INFOMSGS DBCC SHRINKFILE (6,64) WITH NO_INFOMSGS DBCC SHRINKFILE (7,64) WITH NO_INFOMSGS DBCC SHRINKFILE (8,64) WITH NO_INFOMSGS DBCC SHRINKFILE (9,64) WITH NO_INFOMSGS
Листинг скрипта: /* сжатие tempdb */ SET NOCOUNT ON; SET XACT_ABORT ON; SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON;
DECLARE @cmd VARCHAR(1000), @dbid int, @file_id int, @name nvarchar(128), @dbname sysname, @sizeTempDB int;
SET @dbname='tempdb' SET @sizeTempDB = 64; --размер до которого будем сжимать
SELECT @cmd='' SELECT @cmd = @cmd + char(13) + char(10) + 'USE [' + @dbname + ']' SELECT @cmd = @cmd + char(13) + char(10) + '' --Создаем checkpoint, чтобы сбросить на диск кешированные индексы и буферы страницы данных SELECT @cmd = @cmd + char(13) + char(10) + 'CHECKPOINT' SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS' --Чистим кеш хранимых процедур: SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC FREEPROCCACHE WITH NO_INFOMSGS' --Очищаем остальные типы кешей: SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC FREESYSTEMCACHE (''ALL'') WITH NO_INFOMSGS' --Чистим кеш сессий: SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC FREESESSIONCACHE WITH NO_INFOMSGS' --Cжимаем базу tempdb: SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC SHRINKDATABASE ([' + @dbname + ']) WITH NO_INFOMSGS'
В большинстве случаев достаточно выполнить только DBCC FREEPROCCACHE, что позволит сжать базу данных TempDB (т.е. нет необходимости в выполнении DBCC DROPCLEANBUFFERS; DBCC FREESYSTEMCACHE ('ALL'); DBCC FREESESSIONCACHE)
В результате выполнения скрипта мы получаем сжатый до возможного (но не меньше указанного нами) размера файлы базы TempDB:
Дисковое пространство освободилось, MS SQL Server продолжает работать, у пользователей не было прерывания в работе. И это главное! Пользователи работают, а администратору есть над чем задуматься — почему же так вырос TempDB. Какие действия (осознанные или неосознанные) привели к росту базы? Надо разбираться. Но это совсем другая задача, решение которой требует более детальной проработки.
Если у вас остались вопросы, тогда добро пожаловать в 42Clouds! Наши консультанты 1С с радостью помогут разобраться вам во всех вопросах.
После определенных манипуляций с базой возникают ситуации, когда размер ее файлов превышает разумные пределы (либо, как у меня, в два раза больше положенного 103 Гб вместо 65 Гб, и это только файл базы данных). Данная ситуация разрешается простым шринком.
- DBCC shrinkdatabase(N’имя_базы’, TRUNCATE_ONLY) ; — усечение всей базы
- use [имя_базы] DBCC SHRINKFILE (N’имя_базы_Data’, 101); — усечение только файла данных до размера 101 мб
- use [имя_базы] DBCC SHRINKFILE (N’имя_базы_Log’, 0); — усечение только файла транзакций до размера 0 мб
Прежде, чем выполнять шринк базы необходимо выполнить архивацию, но не всей базы, а именно файла транзакций. Только после завершения этой процедуры можно смело выполнять команду shrink, и результат будет достигнут. Надо сказать, что процедура архивации нужна только, если для базы данных установлена модель архивирования Full. В модели Simple log-файл автоматически помечается, как свободный для использования и команда shrink работает без проблем, в модели Full файл становится свободным для использования только после backup-а соответствующего файла.
Та же операция для SQL 2008 будет выглядеть по другому. Так как нет такого ключа как TRUNCATE_ONLY для команды BACKUP, то можно использовать временный перевод базы режим бекапирования SIMPLE, выполнить усечение файла, и вернуть режим FULL. конечно если изначально такой и был:
USE ИмяБазы
ALTER DATABASE ИмяБазы SET RECOVERY SIMPLE
DBCC SHRINKFILE ('ИмяФайлаЛогическое', 10);
ALTER DATABASEИмяБазы SET RECOVERY FULL
Так же, после проведенных манипуляций никогда не помешает сделать проверку базы. Как обычно переводим базу в монопольный режим ( Если необходимо использовать аргументы REPAIR, выполните инструкцию DBCC CHECKDB без параметра восстановления, чтобы узнать требуемый уровень восстановления, и только потом восстановления )
exec sp_dboption 'ИмяБазы','single user','true';
dbcc checkdb ('ИмяБазы',REPAIR_REBUILD);
exec sp_dboption 'ИмяБазы ','single user','false';
Всем привет.
Из за большого размера файла log.ldf, который стал примерно 150Gb и является историей транзакция базы данных SQL, возникла необходимость его уменьшить
Файл позволяет вернуться к любой точке времени и восстановить базу на указанное время.
Т.к. настроены полные бэкапы в планировщике и достаточно часто делаются, то необходимости в этом журнале особо нет, хотя было бы не плохо иметь всегда месячную версию.
Подскажите как через планировщик настроить сжатие и очистку этого файла после успешно сделанного бэкапа?
- Вопрос задан более двух лет назад
- 2101 просмотр
Этот файл не позволяет этого сделать. Правильно настроенное резервное копирование, включающее в себя резервные копии журнала транзакций позволяет восстановить БД на любой (с оговорками) момент времени.
Подскажите как через планировщик настроить сжатие и очистку этого файла после успешно сделанного бэкапа?
Полный бэкап не влияет усечение журнала транзакций. Если ваша база в полной модели восстановления (что, видимо, так), и вы делаете только полные резервные копии - ваш журнал транзакций будет только расти, сколько бы шринков (DBCC SHRINKDATABASE/SHRINKFILE) вы не делали.
Грамотный подход для того, чтобы не страдать от разрастания журнала транзакций: почитать про модели восстановления; виды резервных копий (включая резервные копии журнала транзакций); настроить резервное копирование журнала транзакций с такой частотой, которая обеспечит оптимальные для вас: размер файла журнала транзакций и объём допустимой потери данных; разово обрезать журнал транзакций с помощью DBCC SHRINKFILE.
Быстрый подход: перевести бд в простую модель восстановления (alter database set recovery simple), выполнить инструкцию из первого ответа и забыть про рост журнала транзакций и восстановление на момент времени.
Как сжать или удалить файл лога базы 1с в sql
Решение проблемы которая выглядит следующим образом:
Такая ошибка может возникнуть если на диске недостаточно место. Часто происходит это из-за разросшегося файла log базы 1с.
Удалить или сжать (шринк) лога базы очень просто. Достаточно проделать действия ниже.
Инструкция
Выполнить все шаги нужно для каждой базы данных.
Читайте также: