Alexander A Manaeff -

 
 

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

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

Модератор: UncleFather

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

Сообщение UncleFather » 16 апр 2020 16:19, Чт




Задача:

Имеется 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 КБ) Скачиваний: 238




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





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



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

Сообщение UncleFather » 01 апр 2021 17:42, Чт

Задача:

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

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




Решение:

 Код на 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 КБ) Скачиваний: 65
Аватара пользователя
UncleFather
Site Admin
 
Сообщения: 1435
Зарегистрирован: 17 авг 2004 16:20, Вт



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

Сообщение UncleFather » 01 апр 2021 17:44, Чт

Задача:

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




Решение:

 Код на 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 КБ) Скачиваний: 67





Alexander A. Manaeff©

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

Мои странички:
ВКонтакте
Одноклассники
Youtube
Facebook
Instagram
Изображение
Изображение
Изображение
Изображение
Аватара пользователя
UncleFather
Site Admin
 
Сообщения: 1435
Зарегистрирован: 17 авг 2004 16:20, Вт




Вернуться в Администрирование Windows Server

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

Alexander A Manaeff -
@Mail.ru .