Инструкция: MS SQL shrink/сжатие базы TempDB без перезапуска SQL Server
Как бы много нам не было известно - мы всё равно всегда готовы изучать все новое. В этой статье я покажу вам, как выполнить сжатие временной базы данных TempDB без остановки работающего экземпляра MS SQL Server, не зная ни количества файлов, ни их имена в БД TempDB.
Давайте разберемся что же это? И зачем так необходимо? При работе 1С:Предприятия в клиент-серверном варианте интенсивно используются временные таблицы. Эти таблицы хранятся в системной базе данных TempDB. В процессе работы временная база данных может значительно увеличиваться в размерах и привести к заполнению всего диска.
Причиной увеличения размера базы данных TempDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства из-за наличия активных транзакций, использующих объекты этой базы данных. Неограниченный рост может вызвать проблемы, связанные с сервером, вплоть до остановки MS SQL Server. В результате - недоступность баз, что в свою очередь вызовет остановку в работе всех пользователей 1С использующих базы на этом сервере.
Чтобы не допустить ничего подобного, следует выполнить основные действия по уменьшению размера базы данных TempDB до требуемой величины:
1. Перезапустить MS SQL Server. В этом случае размер базы данных TempDB будет установлен по умолчанию.
2. Сжать базу данных TempDB.
3. Уменьшить размер отдельных файлов.
4. Переместить базу данных TempDB на диск большего размера.
Решил я эту задачу путем уменьшения размера отдельных файлов.
Но для начала немного вводной информации. Временная база данных является системной базой данных, которая используется как для системных, так и для прикладных задач. Вот часть из них:
- Выполнение запросов с инструкциями ORDER BY, GROUP BY или операторами множеств (UNION, INTERSECT, EXCEPT).
- Создание временных таблиц и табличных переменных (например в 1С часто используют #t, #t1 и т.п.).
- Создание и обновление индексов.
- Использование табличных функций.
- Использование триггеров.
- Использование больших объектов (varchar(max), nvarchar(max), varbinary(max), text, ntext, image, xml) как параметров или переменных.
- Выполнение скриптов с использованием курсоров типа static или keyset.
- Уровень изоляции транзакций с версионностью (SNAPSHOT).
- Выполнение команды DBCC CHECK.
- Использование Database mail.
Также 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 для выполнения из командной строки, в результате выполнения которого получаются команды вида:
USE [tempdb]
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
Обратите внимание, что в тексте нет инструкции GO. GO не является ключевым словом T-SQL, это инструкция SQL Server Management Studio, позволяющая разделять отдельные конструкции в скрипте. Параметр 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; --размер до которого будем сжимать
IF OBJECT_ID('tempdb..#tmpdbFile') IS NOT NULL BEGIN DROP TABLE #tmpdbFile END
--создаем и заполняем таблицу с именами логических файлов CREATE TABLE #tmpdbFile (id int IDENTITY(1,1), [dbid] int, [file_id] int, [name] sysname, [dbname] sysname, isdone bit);
INSERT INTO #tmpdbFile ([dbid], [file_id], [name], [dbname], [isdone]) SELECT s_mf.database_id, s_mf.[file_id], s_mf.[name], d.name, 0 FROM sys.databases d Left JOIN sys.master_files s_mf ON d.database_id = s_mf.database_id WHERE db_name(s_mf.database_id) = @dbname --and (s_mf.size * 8.0 / 1024) >= @sizeTempDB ORDER BY s_mf.[file_id];
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'
--Формируем команды на сжатие файлов IF (SELECT COUNT(id) FROM #tmpdbFile) > 0 BEGIN WHILE (SELECT COUNT(id) FROM #tmpdbFile WHERE isdone = 0) > 0 BEGIN SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @file_id = [file_id], @name = [name] FROM #tmpdbFile WHERE isdone = 0 SELECT @cmd = @cmd + char(13) + char(10) + 'DBCC SHRINKFILE (' + CONVERT(varchar(2),@file_id) + ',' + cast(@sizeTempDB as nvarchar) +') WITH NO_INFOMSGS'
UPDATE #tmpdbFile SET isdone = 1 WHERE [dbid] = @dbid AND name = @name
IF OBJECT_ID('tempdb..#tmpdbFile') IS NOT NULL BEGIN DROP TABLE #tmpdbFile END
GO
В большинстве случаев достаточно выполнить только DBCC FREEPROCCACHE, что позволит сжать базу данных TempDB (т.е. нет необходимости в выполнении DBCC DROPCLEANBUFFERS; DBCC FREESYSTEMCACHE ('ALL'); DBCC FREESESSIONCACHE)
В результате выполнения скрипта мы получаем сжатый до возможного (но не меньше указанного нами) размера файлы базы TempDB:
| До выполнения скрипта | После выполнения скрипта |
|---|---|
|
.png)
|
Дисковое пространство освободилось, MS SQL Server продолжает работать, у пользователей не было прерывания в работе. И это главное! Пользователи работают, а администратору есть над чем задуматься — почему же так вырос TempDB. Какие действия (осознанные или неосознанные) привели к росту базы? Надо разбираться. Но это совсем другая задача, решение которой требует более детальной проработки.
Если у вас остались вопросы, тогда добро пожаловать в 42Clouds! Наши консультанты 1С с радостью помогут разобраться вам во всех вопросах.