Technical Article

Dynamic Shrink Log file on server

,

This script helps us to find out which are the largest log files in size so we can try to shrink them to the minimum size.

This script dynamically changes the Recovery Model to simple and then shrinks the file; then it makes the Recovery Model the orignal one.

DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
DBNAME NVARCHAR(1000),
[FileName] NVARCHAR(1000)
)
INSERT INTO @tbl
SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl


WHILE(@MinID <=@MaxID)
BEGIN
SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

SELECT@RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

SELECT@SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+
+N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END
+CHAR(10)
PRINT @SQL
EXEC SP_EXECUTESQL @SQL

SELECT @MinID = @MinID +1
END

Rate

1.78 (9)

Share

Share

Rate

1.78 (9)