Сжатие логов журнала транзакции MS SQL
Автоматический шринк журнала транзакций и перевод базы в простой режим восстановления.
На SQL сервере развернуто порядка 100 тестовых баз. Разворачивают в основном рабочие базы с режимом восстановления “Полный” и периодически забывая переводить в “Простую”. В связи с чем на сервере стали занимать много мест журналы транзакций. Вначале шринк журнала проходил руками, но потом это надоело и я решил это немного автоматизировать. Написал скрипт, который, пробегаясь по всем базам, кроме системных, переводит каждую в “Простой” режим восстановления и делает шринк журнала. Скрипт повесил на задание, для автоматического выполнения по расписанию.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
--------------------------------------------- -- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ -- Условие для выборки, '%' - все базы данных DECLARE @namelike varchar(100) = '%' ------------------------------------------- -- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ DECLARE @SQLString NVARCHAR(4000) DECLARE @DBName varchar(100) DECLARE @DBLogName varchar(100) DECLARE @subject as NVARCHAR(1000) = '' DECLARE @finalmassage as NVARCHAR(4000) = '' ------------------------------------------- -- ТЕЛО СКРИПТА -- Отберем базы для выполнения операций DECLARE DBcursor CURSOR FOR ( SELECT t_Name.name as DatabaseName, t_LogName.name as DatabaseLogName FROM sys.databases as t_Name Inner join sys.master_files as t_LogName on t_Name.database_id = t_LogName.database_id WHERE t_Name.database_id > 4 AND t_Name.state_desc = 'ONLINE' -- база должна быть в сети AND t_Name.name like @namelike -- база должна содержать указанное слово AND t_LogName.type = 1 ) -- Цикл по всем базам, попавшим в выборку OPEN DBcursor FETCH NEXT FROM DBcursor INTO @DBName, @DBLogName WHILE @@FETCH_STATUS = 0 BEGIN -- Переводим базы в простую модель восстановления SET @SQLString = 'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT;' PRINT @SQLString BEGIN TRY EXEC sp_executesql @SQLString END TRY BEGIN CATCH -- Ошбика выполнения операции SET @finalmassage = @finalmassage + 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13) + 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13) + 'Текст ошибки: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(13) + 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13) END CATCH; -- Запускаем сжатие лога базы данных SET @SQLString = 'USE [' + @DBName + ']; DBCC SHRINKFILE([' + @DBLogName + '], 16, truncateonly); ALTER DATABASE [' + @DBName + '] MODIFY FILE (NAME = [' + @DBLogName + '], SIZE = 64MB);' PRINT @SQLString BEGIN TRY EXEC sp_executesql @SQLString END TRY BEGIN CATCH -- Ошбика выполнения операции SET @finalmassage = @finalmassage + 'ОШИБКА сжатия базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13) + 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13) + 'Текст ошибки: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(13) + 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13) END CATCH; -- Следующий элемент цикла FETCH NEXT FROM DBcursor INTO @DBName, @DBLogName END CLOSE DBcursor; DEALLOCATE DBcursor; GO |
Второй вариант:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ declare @db_name nvarchar(100) --имя базы данных declare cursor_size_srv cursor for --выбираем все базы кроме системных SELECT name AS DBName FROM sys.databases where name not in ('master','msdb','model','tempdb') ORDER BY Name; -- Цикл по всем базам, попавшим в выборку OPEN cursor_size_srv FETCH NEXT FROM cursor_size_srv INTO @db_name WHILE (@@FETCH_STATUS=0) BEGIN -- База данных из цикла exec ('declare @logname nvarchar(100) USE [' + @db_name + '] SELECT @logname = [name] FROM sys.database_files where type = 1 ALTER DATABASE [' + @db_name + '] SET RECOVERY SIMPLE DBCC SHRINKFILE (@logname , 10, TRUNCATEONLY)') -- если надо, то обратно режим полного протоколирования /* ALTER DATABASE [' + @db_name + '] SET RECOVERY FULL */ -- Следующая база данных FETCH NEXT FROM cursor_size_srv INTO @db_name END CLOSE cursor_size_srv DEALLOCATE cursor_size_srv |
The code below, get a list of non system databases, set the database to readonly and then shrink the file. I have kept this code in a few SQL Server boxes using SQL Agent Job, where space is always an issue. On Sat/Sun night every week, it start running and shrink all the databases within few hours (depending upon the size of databases):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
In addition, you can find the option “Auto Shrink” in your MS SQL database properties. When you enable this option, the SQL Server will be periodically checking the unused space and reducing the size of the database and log files. Microsoft does not recommend using this option for typical databases, and if you decided to use Auto Shrink, your database should be running in the Full Recovery mode.
You can enable this option in the database parameters in the Automatic section. Just change the Auto Shrink parameter value to True. After you enabled autoshrink, MS SQL will perform automatic compression only if the unused space occupies more than 25% of the total volume size.
This method is applicable to all supported versions of SQL Server: 2005, 2008, 2012, 2014, and 2016, 2017.
This is just one of the ways to quickly reduce the size of the logs. Not the greatest, but very simple and effective.