MS SQL. Изменение параметров для всех баз

Все об администрировании Windows NT/2000/2003/2008/2012 Server (в т.ч. R2) серверов. Групповые политики, домены, безопасность и пр. То, чего не найдешь в бескрайних просторах Интернета. Решения тех проблем, которые не решаются типовыми ответами, которые можно получить в техподдержке Майкрософта - а именно: переустановить продукт или купить какой-ть другой лицензионный диск.


Модератор: UncleFather

Аватара пользователя
UncleFather
Site Admin
Сообщения: 1503
Зарегистрирован: 17 авг 2004 16:20, Вт
Контактная информация:

MS SQL. Изменение параметров для всех баз

Сообщение UncleFather »

Задача:

Имеется Microsoft SQL Server 2014 с большим количеством баз. Необходимо выполнить следующие мероприятия по оптимизации для всех баз:

  1. Очистить и сжать файл журнала транзакций;

  2. Установить шаг автоматического приращения (FILEGROWTH) при увеличении размера файла базы данных равным 100МБ;

  3. Установить шаг автоматического приращения (FILEGROWTH) при увеличении размера файла журнала транзакций базы данных равным 100МБ;

  4. Задать максимальное значение, до которого может увеличиваться размер файла журнала транзакций базы данных (MAXSIZE) равным 2048МБ;

  5. Отключить параметр «Автосжатие» (AUTO_SHRINK);

  6. Отключить параметр «Автозакрытие» (AUTO_CLOSE);


Решение:

Код: Выделить всё

USE MASTER
declare
   @SQL_Query varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128)
 
   declare c1 cursor for
   SELECT  d.name, mf.name as logfile
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   where d.name not in ('master','model','msdb','tempdb')
   and mf.type_desc = 'LOG'   
   open c1
   fetch next from c1 into @dbname, @logfile
   While @@fetch_status <> -1
      begin
 
		SET @SQL_Query = 'USE ' + @dbname + '
		ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE
		DBCC SHRINKFILE (' + @logfile + ');
		ALTER DATABASE ' + @dbname + ' SET RECOVERY FULL
		ALTER DATABASE ' + @dbname + '
		MODIFY FILE
		(NAME=' + @logfile + ',FILEGROWTH=100MB,MAXSIZE = 2048MB);
		ALTER DATABASE ' + @dbname + '
		MODIFY FILE
		(NAME=' + @dbname + ',FILEGROWTH=100MB);
		ALTER DATABASE ' + @dbname + '  SET AUTO_CLOSE OFF WITH NO_WAIT;
		ALTER DATABASE ' + @dbname + '  SET AUTO_SHRINK OFF WITH NO_WAIT;';
		print @SQL_Query
		exec (@SQL_Query)

      fetch next from c1 into @dbname, @logfile
      end
   close c1
   deallocate c1

Скачать SQL-скрипт:

Setting_FileGrowth_And_Maxsize_Of_DB_And_Log_Files.rar
(4.4 КБ) 356 скачиваний
Аватара пользователя
UncleFather
Site Admin
Сообщения: 1503
Зарегистрирован: 17 авг 2004 16:20, Вт
Контактная информация:

Сжатие баз и журналов

Сообщение UncleFather »

Задача:

Необходимо выполнить сжатие самих баз данных, а так же их файлов - файла базы данных и файла журнала. Задача осложняется тем, что имя файла база данных и/или журнала не всегда совпадает с именем базы.

Сжимать необходимо все (кроме системных) пользовательские базы с полной моделью восстановления.


Решение:

Код на SQL

Код: Выделить всё

USE MASTER
declare
   @isql varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128)
 
   declare c1 cursor for 
   SELECT  d.name, mf.name as logfile
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   where recovery_model_desc = 'FULL'
   and d.name not in ('master','model','msdb','tempdb') 
   and (mf.type_desc = 'LOG' OR mf.type_desc = 'ROWS')
   open c1
   fetch next from c1 into @dbname, @logfile
   While @@fetch_status <> -1
      begin

      select @isql = 'DBCC SHRINKDATABASE( ' + @dbname + ' );'
      print @isql
      exec(@isql)

      select @isql = 'USE ' + @dbname + '; DBCC SHRINKFILE( ' + @logfile + ', TRUNCATEONLY );'
      print @isql
      exec(@isql)

      select @isql = 'USE ' + @dbname + '; DBCC SHRINKFILE( ' + @logfile + ' );'
      print @isql
      exec(@isql)
 
      fetch next from c1 into @dbname, @logfile
      end
   close c1
   deallocate c1

Скачать SQL скрипт можно здесь:

Shrinking.rar
(1.57 КБ) 177 скачиваний
Аватара пользователя
UncleFather
Site Admin
Сообщения: 1503
Зарегистрирован: 17 авг 2004 16:20, Вт
Контактная информация:

Изменить модель восстановления БД и сжать файлы

Сообщение UncleFather »

Задача:

Необходимо выполнить изменение модели восстановления на полную для всех (кроме системных) пользовательских баз с неполной моделью восстановления, после чего сжать эти базы данных и файлы журналов БД. Имя файла журнала не обязательно совпадает с именем самой базы данных.


Решение:

Код на SQL

Код: Выделить всё

USE MASTER
declare
   @isql varchar(2000),
   @datbas varchar(128)

   declare c1 cursor for 
   SELECT d.name as datbas
   FROM sys.databases d
   where recovery_model_desc != 'FULL'
   and d.name not in ('master','model','msdb','tempdb')    
   open c1
   fetch next from c1 into @datbas
   While @@fetch_status <> -1
      begin
 
      select @isql = 'ALTER DATABASE ' + @datbas + ' SET RECOVERY FULL'
      print @isql
      exec(@isql)
 
      fetch next from c1 into @datbas
      end
   close c1
   deallocate c1
GO

USE MASTER
declare
   @isql varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128)

   declare c1 cursor for 
   SELECT  d.name, mf.name as logfile
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   where recovery_model_desc = 'FULL'
   and d.name not in ('master','model','msdb','tempdb') 
   and mf.type_desc = 'LOG'   
   open c1
   fetch next from c1 into @dbname, @logfile
   While @@fetch_status <> -1
      begin
 
      select @isql='USE ' + @dbname + ' checkpoint'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
      print @isql
      exec(@isql)
 
      fetch next from c1 into @dbname, @logfile
      end
   close c1
   deallocate c1
GO

Скачать SQL скрипт можно здесь:

Chg2Full&Shrink.rar
(1.63 КБ) 175 скачиваний

Alexander A. Manaeff©

Понравилась статья? Будем крайне признательны за репосты в соцсетях! Материально поддержать проект можно здесь

Мои странички:
ВКонтакте
Одноклассники
Youtube
Facebook
Instagram

Изображение
Изображение
Изображение
Изображение

Ответить