Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DECLARE @rVal AS BIT ;
DECLARE @dbName AS sysname ;
DECLARE @command AS NVARCHAR(MAX)
DECLARE @error AS NVARCHAR(MAX) ;
DECLARE @newline AS NVARCHAR(2) ;
SET @error = N'' ;
SET @newline = NCHAR(13) + NCHAR(10) ;
DECLARE shrinklog CURSOR FAST_FORWARD READ_ONLY
FOR SELECT N'DBCC SHRINKFILE (' + DatabaseName + N'_Log, '
+ CAST(LogFileSizeInMB AS NVARCHAR(10)) + N') ;',
DatabaseName
FROM Maintenance.dbo.BackupControl AS bc
WHERE LogFileSizeInMB IS NOT NULL
OPEN shrinklog
FETCH NEXT FROM shrinklog INTO @command, @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @rval = sp_executesql @command ;
IF @rVal <> 0
SET @error = N'Error truncating log file for database ' + @dbName;
FETCH NEXT FROM shrinklog INTO @command, @dbName
END
CLOSE shrinklog
DEALLOCATE shrinklog
IF @error <> ''
RAISERROR(50000, 16, 1, @error) WITH NOWAIT, LOG, SETERROR ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment