Страница 1 из 1
MS SQL. Изменение параметров для всех баз
Добавлено: 16 апр 2020 16:19, Чт
UncleFather
Задача:
Имеется Microsoft SQL Server 2014 с большим количеством баз. Необходимо выполнить следующие мероприятия по оптимизации для всех баз:
-
Очистить и сжать файл журнала транзакций;
-
Установить шаг автоматического приращения (FILEGROWTH) при увеличении размера файла базы данных равным 100МБ;
-
Установить шаг автоматического приращения (FILEGROWTH) при увеличении размера файла журнала транзакций базы данных равным 100МБ;
-
Задать максимальное значение, до которого может увеличиваться размер файла журнала транзакций базы данных (MAXSIZE) равным 2048МБ;
-
Отключить параметр «Автосжатие» (AUTO_SHRINK);
-
Отключить параметр «Автозакрытие» (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-скрипт:
Сжатие баз и журналов
Добавлено: 01 апр 2021 17:42, Чт
UncleFather
Задача:
Необходимо выполнить сжатие самих баз данных, а так же их файлов - файла базы данных и файла журнала. Задача осложняется тем, что имя файла база данных и/или журнала не всегда совпадает с именем базы.
Сжимать необходимо все (кроме системных) пользовательские базы с полной моделью восстановления.
Решение:
Код: Выделить всё
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 скрипт можно здесь:
Изменить модель восстановления БД и сжать файлы
Добавлено: 01 апр 2021 17:44, Чт
UncleFather
Задача:
Необходимо выполнить изменение модели восстановления на полную для всех (кроме системных) пользовательских баз с неполной моделью восстановления, после чего сжать эти базы данных и файлы журналов БД. Имя файла журнала не обязательно совпадает с именем самой базы данных.
Решение:
Код: Выделить всё
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 скрипт можно здесь: