Skip to content

Instantly share code, notes, and snippets.

@antonio-leonardo
Last active July 18, 2019 19:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save antonio-leonardo/796c9f87cdb93c6db4f2c94c4814622e to your computer and use it in GitHub Desktop.
Save antonio-leonardo/796c9f87cdb93c6db4f2c94c4814622e to your computer and use it in GitHub Desktop.
Sql Server: Cursor that automate shrink all logs
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