Ads

22 August 2012

One More - Script To Shrink Log Files


DBCC SQLPERF(logspace) --Optional
GO
DECLARE @logsize AS INT = 0; -- MB
DECLARE @databasename AS VARCHAR(256);
DECLARE @filename VARCHAR(256);
DECLARE @cmd VARCHAR(MAX);
DECLARE curdb CURSOR FOR
    SELECT f.name AS filename, d.name AS databasename
    FROM msdb.sys.master_files f
        INNER JOIN master.sys.sysdatabases d
        ON d.dbid = f.database_id
    WHERE type = 1 AND state = 0 AND size > @logsize AND database_id > 4  
OPEN curdb
FETCH NEXT FROM curdb INTO @filename, @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT @filename
    SET @cmd = ('USE [' + @databasename + ']; ')
    SET @cmd = @cmd + 'DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@logsize AS VARCHAR) + ');';
    EXEC (@cmd)
    FETCH NEXT FROM curdb INTO @filename, @databasename
END
DEALLOCATE curdb
GO
DBCC SQLPERF(logspace) --Optional If you require to send out put in file
GO

No comments:

Post a Comment