Shrink the Log file
The article contains the stored procedure that shrink the Log file successfully and no need to any down time. It shrinks the log file on the fly.
The Stored procedure is ready to use and it is my recommendation to juniors, please don't make any changes on it.
/*
Date: 03-April-2012
by : joydeep Das
Note: Shrint the Log file on runtime
*/
IF EXISTS (SELECT *
FROM sysobjects
WHERE type = 'P'
AND name = 'up_RUN_LOGSHRINKER')
BEGIN
DROP PROCEDURE up_RUN_LOGSHRINKER
END
GO
CREATE Procedure [dbo].[up_RUN_LOGSHRINKER]
(
@P_DBName VARCHAR(MAX) = NULL
)
AS
DECLARE @sqlString AS NVARCHAR(MAX)
DECLARE @LogincalFileStr AS SYSNAME
DECLARE @dbName AS VARCHAR(MAX)
DECLARE @dbRecovMod AS VARCHAR(MAX)
DECLARE @FlgStat AS INT
BEGIN
SET NOCOUNT ON;
SET @FlgStat = 0;
SET @sqlString = 'ALTER DATABASE ' + @P_DBName + ' SET RECOVERY SIMPLE'
EXEC (@sqlString)
SELECT @dbName=RTRIM(LTRIM([name])),
@dbRecovMod=RTRIM(LTRIM([recovery_model_desc]))
FROM sys.databases WHERE [name] = DB_NAME()
IF @dbName = @P_DBName AND @dbRecovMod = 'SIMPLE'
BEGIN
SELECT @LogincalFileStr = [Name] FROM sys.database_filesWHERE type = 1
IF ISNULL(@LogincalFileStr,'')<>''
BEGIN
DBCC SHRINKFILE(@LogincalFileStr, 1)
SET @FlgStat = 1
END
END
SET @sqlString = 'ALTER DATABASE ' + @P_DBName + ' SET RECOVERY FULL'
EXEC (@sqlString)
SELECT @dbName = RTRIM(LTRIM([name])),
@dbRecovMod = RTRIM(LTRIM([recovery_model_desc]))
FROM sys.databases WHERE [name] = DB_NAME()
IF @dbName = @P_DBName AND @dbRecovMod = 'FULL'
BEGIN
SET @FlgStat = 1
END
ELSE
BEGIN
SET @FlgStat = 0
END
IF @FlgStat=1
BEGIN
PRINT 'LOG-Shrink Successfull'
END
ELSE
BEGIN
PRINT 'LOG-Shrink NOT Successfull -- Contact System Admin'
END
END
GO
-- TO run this for Specified DB
USE my_db
EXEC up_RUN_LOGSHRINKER
@P_DBName = 'my_db'
Hope you like it.
