You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.

Загрузка...

dbcc shrinkfile ms sql: примеры

  1. Выполнить DBCC SHRINKFILE мышкой;
  2. Использовать скрипт без курсора;
  3. Использовать скрипт с курсором.

Перед выполнение всего того, что здесь описано сделайте backup баз данных и проверьте работоспособность бэкапов!

Изначально было неоходимо просто уменьшить транзакционные лог-файлы (ldf) баз данных, которые сильно разраслись, но впоследствии могут оставаться процентов на 90 пустыми занимая почти всё место на дисках. 

Если на сервере 1 - 2 базы, то можно сильно не заморачиваться и сделать всё это вручную. Запускаем ssms (Microsoft SQL Server Management Studio), подключаемся к субд, открываем список баз данных и правой кнопкой мыши кликаем на нужную базу данных затем переходим по диалоговым окнам: Задачи->Сжать->Файлы и кликаем на Файлы или в английском варианте: Tasks->Shrink->Files  - кликаем на Files:

и в английском варианте:

В окне Сжатие файла (Shrink File) в раскладке Тип Файла (File type) выбираем, либо Журнал (Log), либо Данные (Data), выберем Журнал (Log) для эксперимента:

английский вариант

Если всё устраивает и нет неоходимости заморачиваться, то можно просто нажать OK и где это там, под капотом SQL Server'а запустится код DBCC SHRINKFILE, но мы пойдём другим путём.

 Жмём на кнопку Скрипт (Script):

англ. вариант:

После нажатия на кнопку Скрипт (Script) сгенерируется код и откроется в новом окне SSMS:

англ. версия

Чтобы запустить код, что в руссифицированной версии, что в английской версии достаточно нажать F5. Но опять же есть нюанс, он заключается в том, что у базы может быть более одного лог файла (ldf) и тем более  может более одно файла самой базы данных (mdf, ndf).

В этом случае с моей точки зрения нужно переходить к динамическому SQL.

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

Список баз получаем из системной таблицы sys.databases:


SELECT name
	, database_id
FROM sys.databases;

Получается что первые 4 базы системые, трогать их в текущей задаче думаю смысла нет. Отфильровываем системные: WHERE database_id > 4


SELECT name
	, database_id
FROM sys.databases
WHERE database_id > 4;

Выполняем код  - F5

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

Далее нужно найти логические имена файлов с помощью системной таблицы sys.database_files, но её нужно запускать, либо в контексте базы данных, либо писать полный ну или почти полный квалификатор:


SELECT *
FROM sys.database_files

SELECT выполнился в контексте базы master.

Дополним клаузулу FROM из предыдущего SELECT'а именем тестовой базы данных (FROM [TSQLV6].sys.database_files)  и выполним его:

Теперь используем две системные таблицы и динамический SQL составляем скрипт:


-- Источник https://itdiary.ru/subd/adm-sql-server/shrinkfile/
-- Данный скрипт подходит для небольшого количества баз, 
-- если его запускать из SSMS
-- т.к. в ssms @SQL NVARCHAR(MAX) ограничена 4000 символами в юникоде
-- либо данный код завернуть в процедуру

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @File NVARCHAR(64)= 'LOG'; -- 'LOG' - лог файлы базы, 'ROWS' - файлы базы, 'NULL' - все файлы базы

IF OBJECT_ID('tempdb..#logfilefordb','u')  IS NOT NULL
	DROP TABLE #logfilefordb;

CREATE TABLE #logfilefordb 
(
	  dbname NVARCHAR(255)  NOT NULL
	, fname NVARCHAR(255)  NOT NULL
	, CONSTRAINT UX_db_logfile UNIQUE (dbname, fname)
)

SELECT @SQL+='SELECT '''+[name]+''' AS db_name'+
			 ', name AS fname '+
			 'FROM ['+[name]+'].sys.database_files ' +
			 'WHERE type_desc = COALESCE(NULLIF('''+@File+''',''NULL''), type_desc);' + CHAR(13) + CHAR(10)
FROM sys.databases
WHERE database_id > 4;

PRINT @SQL;

INSERT INTO #logfilefordb (dbname, fname)
EXEC(@SQL);

SET @SQL = '';

SELECT 
	@SQL += 'USE ['+dbname+'];' + CHAR(13) + CHAR(10) +
	'DBCC SHRINKFILE (N'''+fname+''' , 0, TRUNCATEONLY);' + CHAR(13) + CHAR(10)
FROM  #logfilefordb;

PRINT @SQL;
EXEC(@sql);

Смотрим результат:

Конструкция WHERE type_desc = COALESCE(NULLIF('LOG','NULL'), type_desc) - это аналог IF ... ELSE:

NULLIF('LOG','NULL') - данная функция возвращает NULL тогда и только тогда, когда оба парамера одинаковые, в противном случае NULLIF возвращает первый параметр, в данном случае это 'LOG'

После того, как отработала функция NULLIF она вернула значение, либо NULL, либо 'LOG', либо 'ROWS':

Если передаётся параметр 'NULL' то клаузула WHERE будет интерпретироваться как WHERE type_desc = type_desc, т.е всегда будет ИСТИНА(TRUE) и все логические файлы будут обработаны: и лог файлы (.ldf), и файлы баз данных(.mdf, .ndf).

Если передаётся параметр 'LOG' то клаузула WHERE будет интерпретироваться как WHERE type_desc = 'LOG', только транзакционные лог-файлы (.ldf) баз будут обработаны.

Если передаётся параметр 'ROWS' то клаузула WHERE будет интерпретироваться как WHERE type_desc = 'ROWS', основные файлы (.mdf, .ndf) баз будут обработаны.

Теперь вместо 'LOG' установим 'NULL' и запустим:

Если переменная  @File равна  'NULL', то сокращаются все файлы баз: и лог-файлы, и все остальные.

Теперь  установим 'ROWS' и запустим:

В результате обработаны только основные файлы баз, а лог-файлы не были затронуты.

Теперь 2-й скрипт, тоже с динамическим SQL, но с использование курсора (CURSOR). Сразу скажу, что CURSOR не приветствуется у разработчиков, но покажу его в качестве альтернативы:


-- Источник https://itdiary.ru/subd/adm-sql-server/shrinkfile/

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @File NVARCHAR(64)= 'LOG'; -- 'LOG' - лог файлы базы, 'ROWS' - файлы базы, 'NULL' - все файлы базы
DECLARE @DBname NVARCHAR(MAX);
DECLARE @LogicalFileName NVARCHAR(MAX);


IF OBJECT_ID('tempdb..#logfilefordb','u')  IS NOT NULL
	DROP TABLE #logfilefordb;

CREATE TABLE #logfilefordb 
(
	  dbname NVARCHAR(225) NOT NULL
	, fname NVARCHAR(225) NOT NULL
	, CONSTRAINT UX_db_logfile UNIQUE (dbname, fname)
)

DECLARE database_name_u  CURSOR READ_ONLY FORWARD_ONLY FOR 
	SELECT DISTINCT [name] AS dbname
	FROM sys.databases
	WHERE database_id > 4
	ORDER BY dbname ASC;

	OPEN database_name_u

	FETCH NEXT FROM database_name_u
	INTO @DBname

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @SQL = 'SELECT ''' + @dbname + ''' AS db_name'+
				 ', name AS fname '+
				 'FROM [' + @dbname + '].sys.database_files ' +
				 'WHERE type_desc = COALESCE(NULLIF(''' + @File + ''',''NULL''), type_desc);' + CHAR(13) + CHAR(10)
	
		PRINT @SQL;
		INSERT INTO #logfilefordb (dbname, fname)
		EXEC(@SQL);

		FETCH NEXT FROM database_name_u
		INTO @DBname

	END
CLOSE database_name_u;
DEALLOCATE database_name_u;


DECLARE shrink_files  CURSOR READ_ONLY FORWARD_ONLY FOR 
	SELECT dbname, fname
	FROM  #logfilefordb ORDER BY dbname, fname;

	OPEN shrink_files

	FETCH NEXT FROM shrink_files
	INTO @DBname, @LogicalFileName

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @SQL = 'USE [' + @DBname + '];' + CHAR(13) + CHAR(10) +
		    'DBCC SHRINKFILE (N''' + @LogicalFileName + ''' , 0, TRUNCATEONLY);' + CHAR(13) + CHAR(10)
	
		PRINT @SQL;
		EXEC(@SQL);

		FETCH NEXT FROM shrink_files
		INTO @DBname, @LogicalFileName

	END
CLOSE shrink_files;
DEALLOCATE shrink_files;

Результат выполнения:

Обратите внимание, что в первом скрипте без курсора динамический SQL сначала формируется весь , затем исполняется.

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

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

PS:Перед использование всего, что описано выше на рабочем сервере, потренируйтесь на тренировочном стэнде/виртуалке, обычном хосте. Если вы решили использовать вышепреведённый код, вы это делаете на свой страх и риск.

Предыдущая статья
dbcc shrinkfile ms sql: примеры