There are some common reasons why a transaction log might not shrink when you use the DBCC SHRINKFILE or DBCC SHRINKDATABASE command.
Please rerfer to the following link http://support.microsoft.com/kb/q256650/
SET NOCOUNT ON
DECLARE @LogicalFileName SYSNAME,
@MaxMinutes INT,
@NewSize INT,
@Factor FLOAT
USE
--Use sp_helpfile to identify the logical file name that you want to shrink.
SET @LogicalFileName = 'databasename_Log';
--Limit on time allowed to wrap log in minutes
SET @MaxMinutes = 5;
--Ideal size of logfile in MB
SET @NewSize =100;
SET @Factor = 1.0;
DECLARE @OriginalSize INT,
@StringData VARCHAR(500)
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName;
SELECT @StringData = 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName;
PRINT @StringData;
PRINT ''
--Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
DROP TABLE [DummyTrans]
CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );
-- Wrap log and truncate it.
DECLARE @Counter INT,
@MaxCount INT,
@StartTime DATETIME,
@TruncLog VARCHAR(500)
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY';
EXEC (@TruncLog)
-- Configure limiter
IF @OriginalSize / @Factor > 50000
SET @MaxCount = 50000
ELSE
SET @MaxCount = @OriginalSize * @Factor
-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @MaxCount AS VARCHAR(10) )
PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'
PRINT ''
SET @Counter = 0;
SET @StartTime = GETDATE();
--loop the padding code to reduce the log while
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Outer loop.
--pad out the logfile a page at a time while
-- number of pages padded does not exceed our maximum page padding limit
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@Counter < @MaxCount) AND
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Inner loop
INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE FROM DummyTrans
SELECT @Counter = @Counter + 1
--Every 1,000 cycles tell the user what is going on
IF ROUND( @Counter , -3 ) = @Counter
BEGIN
PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
END
END
--See if a trunc of the log shrinks it.
EXEC( @TruncLog )
END
PRINT ''
SELECT @StringData = 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName;
PRINT @StringData
PRINT ''
DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF