How to do a one-time log shrink on a database with transactional replication

Question

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was hired.

The primary has a log shipping backup job that runs every 15 minutes. The primary has copy and restore jobs that run every 15 minutes.

When I attempt to shrink the log, I get "Cannot shrink log file 2 (DatabaseName_log) because the logical log file located at the end of the file is in use." I have retried this several times 15 minutes apart and even days apart, but always get the same result.

DBCC LOGINFO shows 784 VLFs, with only the first 245 and the last on with a status of 2. p_WhoIsActive shows that the longest open transaction has been running for less than 2 hours (long transactions are not unusual here due to a third-party Microsoft Access app with ODBC driver issues).

How can I successfully shrink this log (without creating an outage for users)?

Thanks, Mark

Answer 1

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

Answer 2

You could potentially run a script such as this to attempt the shrink operation once every minute until it completes, or runs 1,000 times. As always, use at your own risk - and test this first on a non-production environment.

USE tempdb;  /*  CHANGE THIS TO DESIRED DATABASE */
GO
DECLARE @DesiredSizeInMB INT;
SET @DesiredSizeInMB = 1;

DECLARE @T TABLE (
    CurrentSize BIGINT
);
DECLARE @LogFileName VARCHAR(255);
DECLARE @LoopCount INT;
DECLARE @MaxLoops INT;
DECLARE @OK BIT;

SELECT @LogFileName = df.name
FROM sys.database_files df
WHERE df.type_desc = 'LOG';

SET @LoopCount = 0;
SET @MaxLoops = 1000;
WHILE @LoopCount < @MaxLoops
BEGIN
    SET @OK=1;
    BEGIN TRY
        DELETE FROM @T;
        INSERT INTO @T
        SELECT size * 8192 / 1048576E0
        FROM sys.database_files df
        WHERE df.type_desc = 'LOG';
        DBCC SHRINKFILE (@LogFileName, @DesiredSizeInMB);
        /* If the now-current size of the log file is with 1MB of the target size, break */
        IF (SELECT CurrentSize * 8192 / 1048576E0 FROM @T) <= @DesiredSizeInMB + 1 
            BREAK;
        ELSE
            SET @OK = 0;
    END TRY
    BEGIN CATCH
        SET @OK=0;
        PRINT ERROR_MESSAGE();
    END CATCH
    IF @OK = 1 BREAK; /* IF the command didn't fail, then exit the loop */
    WAITFOR DELAY '00:00:30'
    SET @LoopCount = @LoopCount +1;
END

Answer 3

When testing my backup plan on a VM test server which was disconnected from the network I was running a backup and then used the shrink command only. I use a SIMPLE recover model, still I had to force the setting of the database to SIMPLE mode, only then I could shrink my t-logs. The conclusion seems that if you have a database in SIMPLE recover model, not FULL you still need these commands: ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE([logfilename], 1)