Как быстро сократить все файлы для всех баз данных?

В SQL Server (2008 в этом случае), как я могу быстро сжать все файлы, как журнал, так и данные, для всех баз данных в экземпляре? Я мог бы пройти через SSMS и щелкнуть правой кнопкой мыши каждый и выбрать «Задачи» -> Сократите, но я ищу что-то быстрее.

Я написал сценарии сценариев «Создать базу данных» и забыл, что у них были баллонные размеры для дефолтов, и для этого файла не требуется достаточно много места для этих файлов.

39 голосов | спросил jcolebrand 6 Jpm1000000pmThu, 06 Jan 2011 21:04:39 +030011 2011, 21:04:39

6 ответов


46

Когда вы выполняете «Задачи -> Shrink» из графического интерфейса, он фактически выдает команду DBCC SHRINKDATABASE за кулисами. Попробуй. Когда появится диалоговое окно, не нажимайте кнопку «ОК». Вместо этого нажмите кнопку «Сценарий». Вы увидите команду в окне запроса. Объедините это с запросом на sys.databases (оставьте мастер и msdb), и вы можете сделать скрипт для сжатия всех баз данных.

Например (взято из комментария jcolebrand):

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

Скопируйте результат этого запроса и запустите его, чтобы сжать все ваши файлы.

ответил Larry Coleman 6 Jpm1000000pmThu, 06 Jan 2011 21:55:09 +030011 2011, 21:55:09
20

Как насчет одной строки инструкции sql?

Прочитайте this очень интересное сообщение в блоге перед выполнением следующего SQL-запроса.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'
ответил CoderHawk 7 Jam1000000amFri, 07 Jan 2011 10:35:17 +030011 2011, 10:35:17
14

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

Более быстрый способ сжать файл базы данных следующий:

  • Назначьте новую файловую группу в базу данных
  • Сделайте эту файловую группу такой большой, как она должна быть (используйте sp_spaceused, чтобы определить, насколько велика)
  • Восстановить все индексы этой новой файловой группы
  • Отбросить старую файловую группу

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

Этот метод также имеет дополнительное преимущество для дефрагментации ваших индексов в процессе.

ответил Thomas Kejser 5 Jpm1000000pmSun, 05 Jan 2014 21:28:44 +040014 2014, 21:28:44
10

Я немного запросил запрос на сжатие только LOG по мере его запроса:

set nocount on  
SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'
ответил Frankachela 14 FebruaryEurope/MoscowbThu, 14 Feb 2013 19:56:51 +0400000000pmThu, 14 Feb 2013 19:56:51 +040013 2013, 19:56:51
1

Код ниже, получить список несистемных баз данных, настроить базу данных на чтение, а затем сжать файл. Я сохранил этот код в нескольких блоках SQL Server, используя SQL Agent Job, где пространство всегда является проблемой. В субботу /ночь ночи каждую неделю он запускает и сокращает все базы данных в течение нескольких часов (в зависимости от размера баз данных).

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c
ответил Muhammad Sharjeel Ahsan 4 Jam1000000amFri, 04 Jan 2013 02:31:20 +040013 2013, 02:31:20
0

Сократить все файлы журналов, кроме master, model, msdb:

EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
IF ''?'' NOT IN (''master'', ''model'', ''msdb'')
BEGIN
USE [?]
SELECT @sqlcommand = ''DBCC SHRINKFILE (N'''''' + 
name
FROM [sys].[database_files]
WHERE type_desc = ''LOG''
SELECT @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
END'
ответил Emrah Sağlam 7 FebruaryEurope/MoscowbWed, 07 Feb 2018 09:20:55 +0300000000amWed, 07 Feb 2018 09:20:55 +030018 2018, 09:20:55

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132