Last active
July 18, 2019 19:45
-
-
Save antonio-leonardo/796c9f87cdb93c6db4f2c94c4814622e to your computer and use it in GitHub Desktop.
Sql Server: Cursor that automate shrink all logs
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
SET NOCOUNT ON; | |
DECLARE @name VARCHAR(MAX) | |
,@DBName SYSNAME | |
,@LogicalLogFile SYSNAME | |
,@SQL_CMD_ALTER_0 VARCHAR(MAX); | |
DECLARE clear_logs_cursor CURSOR FOR | |
SELECT name FROM master.dbo.sysdatabases | |
WHERE name NOT IN ('master','model','msdb','tempdb'); | |
OPEN clear_logs_cursor; | |
FETCH NEXT FROM clear_logs_cursor INTO @name; | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
PRINT 'Start shrink process at a Log Database ' + @name; | |
SET @DBName = @name; | |
-- Log file | |
SELECT @LogicalLogFile = name | |
FROM sys.master_files | |
WHERE database_id = db_id(@DBName) | |
AND type = 1; | |
SET @SQL_CMD_ALTER_0 = 'USE "' + @name + '"; | |
ALTER DATABASE "' + @name + '" SET RECOVERY SIMPLE; | |
DBCC SHRINKFILE ("' + @LogicalLogFile + '", 1); | |
ALTER DATABASE "' + @name + '" SET RECOVERY FULL;'; | |
EXEC(@SQL_CMD_ALTER_0); | |
FETCH NEXT FROM clear_logs_cursor INTO @name; | |
PRINT 'Success! The next database to be shrinked is ' + @name; | |
END; | |
PRINT 'Ends all process'; | |
CLOSE clear_logs_cursor; | |
DEALLOCATE clear_logs_cursor; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment