Shrink Database Log file in SQL Server

 

Method 1 : This script will shrink the all log files of specific database by  change the recovery modle to simple  and again it rever back the recovery model

 

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 sb.name = 'TEST2008'  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

 

Method 2: This script will shrink the all databases log files, depends on the size of log file by  change the recovery modle to simple  and again it rever back the recovery model

 

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