Created
May 21, 2025 05:44
-
-
Save it2-torchbearer/1d07f3d9becbae2c2f1e5816331e907b to your computer and use it in GitHub Desktop.
Очистка логов всех баз MSSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Declare @name varchar(100) | |
| declare @qu as varchar(1200) | |
| declare icur cursor fast_forward for | |
| SELECT name | |
| FROM sys.databases | |
| WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') | |
| --and recovery_model_desc = 'FULL' | |
| open icur | |
| fetch next from icur into @name | |
| While @@Fetch_Status = 0 | |
| Begin | |
| Set @qu='use [' + @name + '] Declare @logname varchar(64), @size int' | |
| Set @qu=@qu + ' Set @logname = (SELECT [name] FROM [sys].[database_files] where type_desc=''LOG'')' | |
| Set @qu=@qu + ' Set @size = (SELECT max_size FROM [sys].[database_files] where type_desc=''LOG'') * 0.7/128' | |
| Set @qu=@qu + ' ALTER DATABASE [' + @name + '] SET RECOVERY SIMPLE DBCC SHRINKFILE (@logname, 7)' | |
| Set @qu=@qu + ' ALTER DATABASE [' + @name + '] SET RECOVERY FULL' | |
| Exec (@qu) | |
| Set @qu = '' | |
| fetch next from icur into @name | |
| END | |
| close icur | |
| SELECT name | |
| FROM sys.databases | |
| WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') | |
| --and recovery_model_desc = 'SIMPLE' | |
| open icur | |
| fetch next from icur into @name | |
| While @@Fetch_Status = 0 | |
| Begin | |
| Set @qu='use [' + @name + '] Declare @logname varchar(64), @size int' | |
| Set @qu=@qu + ' Set @logname = (SELECT [name] FROM [sys].[database_files] where type_desc=''LOG'')' | |
| Set @qu=@qu + ' Set @size = (SELECT max_size FROM [sys].[database_files] where type_desc=''LOG'') * 0.7/128' | |
| Set @qu=@qu + ' ALTER DATABASE [' + @name + '] SET RECOVERY SIMPLE DBCC SHRINKFILE (@logname, 7)' | |
| Exec (@qu) | |
| Set @qu = '' | |
| fetch next from icur into @name | |
| END | |
| close icur | |
| deallocate icur | |
| DBCC SHRINKDATABASE (TEMPDB); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment