Шринк логов (сжать логи) у всех баз на сервере

Необходимо сжать логи у всех баз на сервере

declare @db nvarchar(10)
declare @sql nvarchar(256)
declare cursor_size_srv cursor for
SELECT  --@@SERVERNAME AS Server ,
        name AS DBName 
        --recovery_model_Desc AS RecoveryModel ,
        --Compatibility_level AS CompatiblityLevel ,
        --create_date ,
        --state_desc
FROM    sys.databases
where name not in ('master','msdb','model','tempdb') 
--and recovery_model_desc = 'full'
ORDER BY Name; 
 
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db
WHILE (@@FETCH_STATUS=0)
BEGIN
set @sql = 'USE '+@db+'
GO
DBCC SHRINKFILE (N'''+@db+'_log'' , 0, TRUNCATEONLY)
GO'
print (@sql);
FETCH NEXT FROM cursor_size_srv INTO @db
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv

Результат скопировать и выполнить в новом запросе

USE rss1
GO
DBCC SHRINKFILE (N'rss1_log' , 0, TRUNCATEONLY)
GO
USE rss10
GO
DBCC SHRINKFILE (N'rss10_log' , 0, TRUNCATEONLY)
GO
USE rss11
GO
DBCC SHRINKFILE (N'rss11_log' , 0, TRUNCATEONLY)
GO
USE rss12
GO
DBCC SHRINKFILE (N'rss12_log' , 0, TRUNCATEONLY)
GO
USE rss13
GO
DBCC SHRINKFILE (N'rss13_log' , 0, TRUNCATEONLY)
GO
USE rss14
GO
DBCC SHRINKFILE (N'rss14_log' , 0, TRUNCATEONLY)
GO
USE rss16
GO
DBCC SHRINKFILE (N'rss16_log' , 0, TRUNCATEONLY)
GO
USE rss17
GO
DBCC SHRINKFILE (N'rss17_log' , 0, TRUNCATEONLY)
GO
USE rss18
GO
DBCC SHRINKFILE (N'rss18_log' , 0, TRUNCATEONLY)
GO
USE rss2
GO
DBCC SHRINKFILE (N'rss2_log' , 0, TRUNCATEONLY)
GO
USE rss20
GO
DBCC SHRINKFILE (N'rss20_log' , 0, TRUNCATEONLY)
GO
USE rss21
GO
DBCC SHRINKFILE (N'rss21_log' , 0, TRUNCATEONLY)
GO
USE rss22
GO
DBCC SHRINKFILE (N'rss22_log' , 0, TRUNCATEONLY)
GO
USE rss23
GO
DBCC SHRINKFILE (N'rss23_log' , 0, TRUNCATEONLY)
GO
USE rss24
GO
DBCC SHRINKFILE (N'rss24_log' , 0, TRUNCATEONLY)
GO
USE rss25
GO
DBCC SHRINKFILE (N'rss25_log' , 0, TRUNCATEONLY)
GO
USE rss26
GO
DBCC SHRINKFILE (N'rss26_log' , 0, TRUNCATEONLY)
GO
USE rss27
GO
DBCC SHRINKFILE (N'rss27_log' , 0, TRUNCATEONLY)
GO
USE rss28
GO
DBCC SHRINKFILE (N'rss28_log' , 0, TRUNCATEONLY)
GO
USE rss29
GO
DBCC SHRINKFILE (N'rss29_log' , 0, TRUNCATEONLY)
GO
USE rss31
GO
DBCC SHRINKFILE (N'rss31_log' , 0, TRUNCATEONLY)
GO
USE rss33
GO
DBCC SHRINKFILE (N'rss33_log' , 0, TRUNCATEONLY)
GO
USE rss34
GO
DBCC SHRINKFILE (N'rss34_log' , 0, TRUNCATEONLY)
GO
USE rss38
GO
DBCC SHRINKFILE (N'rss38_log' , 0, TRUNCATEONLY)
GO
USE rss39
GO
DBCC SHRINKFILE (N'rss39_log' , 0, TRUNCATEONLY)
GO
USE rss4
GO
DBCC SHRINKFILE (N'rss4_log' , 0, TRUNCATEONLY)
GO
USE rss40
GO
DBCC SHRINKFILE (N'rss40_log' , 0, TRUNCATEONLY)
GO
USE rss43
GO
DBCC SHRINKFILE (N'rss43_log' , 0, TRUNCATEONLY)
GO
USE rss46
GO
DBCC SHRINKFILE (N'rss46_log' , 0, TRUNCATEONLY)
GO
USE rss47
GO
DBCC SHRINKFILE (N'rss47_log' , 0, TRUNCATEONLY)
GO
USE rss48
GO
DBCC SHRINKFILE (N'rss48_log' , 0, TRUNCATEONLY)
GO
USE rss5
GO
DBCC SHRINKFILE (N'rss5_log' , 0, TRUNCATEONLY)
GO
USE rss50
GO
DBCC SHRINKFILE (N'rss50_log' , 0, TRUNCATEONLY)
GO
USE rss51
GO
DBCC SHRINKFILE (N'rss51_log' , 0, TRUNCATEONLY)
GO
USE rss53
GO
DBCC SHRINKFILE (N'rss53_log' , 0, TRUNCATEONLY)
GO
USE rss54
GO
DBCC SHRINKFILE (N'rss54_log' , 0, TRUNCATEONLY)
GO
USE rss56
GO
DBCC SHRINKFILE (N'rss56_log' , 0, TRUNCATEONLY)
GO
USE rss57
GO
DBCC SHRINKFILE (N'rss57_log' , 0, TRUNCATEONLY)
GO
USE rss57_temp
GO
DBCC SHRINKFILE (N'rss57_temp_log' , 0, TRUNCATEONLY)
GO
USE rss58
GO
DBCC SHRINKFILE (N'rss58_log' , 0, TRUNCATEONLY)
GO
USE rss6
GO
DBCC SHRINKFILE (N'rss6_log' , 0, TRUNCATEONLY)
GO
USE rss62
GO
DBCC SHRINKFILE (N'rss62_log' , 0, TRUNCATEONLY)
GO
USE rss63
GO
DBCC SHRINKFILE (N'rss63_log' , 0, TRUNCATEONLY)
GO
USE rss66
GO
DBCC SHRINKFILE (N'rss66_log' , 0, TRUNCATEONLY)
GO
USE rss7
GO
DBCC SHRINKFILE (N'rss7_log' , 0, TRUNCATEONLY)
GO
USE rss8
GO
DBCC SHRINKFILE (N'rss8_log' , 0, TRUNCATEONLY)
GO
USE rss9
GO
DBCC SHRINKFILE (N'rss9_log' , 0, TRUNCATEONLY)
GO

Similar Posts:

• перевод всех бд в simple
• Как добавить права чтения или записи на базу, сразу многим пользователям.
• Сжать лог у всех баз на сервере
• Как мониторить цепочку lsn лог шипинга ( log shipping ) на разрыв.
• Как сделать бэкап множества баз mssql на сетевое хранилище с созданием папок под бэкап. И после удалить эти базы.