Инструкция: MS SQL shrink/сжатие базы TempDB без перезапуска SQL Server

Как бы много нам не было известно — мы всё равно всегда готовы изучать всё новое. В этой статье я покажу вам как выполнить сжатие временной базы данных TempDB без остановки работающего экземпляра MS SQL Server и при этом не зная ни количества файлов, ни их имена в БД TempDB.

Давайте разберемся что же это? И зачем так необходимо?

При работе 1С:Предприятия в клиент-серверном варианте интенсивно используются временные таблицы. Эти таблицы хранятся в системной базе данных TempDB. В процессе работы временная база данных может значительно увеличиваться в размерах и привести к заполнению всего диска.

Причиной увеличения размера базы данных TempDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства из-за наличия активных транзакций, использующих объекты этой базы данных. Неограниченный рост может вызвать проблемы, связанные с сервером, вплоть до остановки MS SQL Server. В результате — недоступность баз. Что в свою очередь вызовет остановку в работе всех пользователей 1С использующих базы на этом сервере.

Так вот, чтобы не допустить ничего подобного и ужасного следует выполнить следующие основные действия по уменьшению размера базы данных 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:

На практике TempDB состоит из нескольких файлов. На различных серверах их название и кол-во отличаются см. Рис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

END
END;
--print (@cmd);
exec (@cmd);

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:

До выполнения скрипта	После выполнения скрипта

Дисковое пространство освободилось, MS SQL Server продолжает работать, у пользователей не было прерывания в работе. И это главное! Пользователи работают, а администратору есть над чем задуматься — почему же так вырос TempDB. Какие действия (осознанные или неосознанные) привели к росту базы? Надо разбираться. Но это совсем другая задача, решение которой требует более детальной проработки.