2

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

  • 1
    What does SELECT d.name, d.log_reuse_wait_desc FROM sys.databases d say about the DB in question? The VLF at the end marked as status 2 is the problem. The transactions in that VLF need to be backed up, and transferred to the secondary, then the VLF marked as status 0 before you can shrink the file. – Max Vernon May 8 '14 at 17:26
  • I assume you've seen this? dba.stackexchange.com/questions/29829/… – Max Vernon May 8 '14 at 17:30
  • Max, I have read that other Q&A. The log_reuse_wait_desc is "REPLICATION". The transactions seem to be successfully shipped and restored on the secondary every 15 minutes, so I don't know why that last VLF is always showing as active. – Mark Freeman May 8 '14 at 18:49
  • If there are a lot of transactions occurring 24x7, replication will not ever be completely caught up. Do you have any Window of time where you can prevent transactions occurring? If you do, that is the time to do the shrink. – Max Vernon May 8 '14 at 18:51
  • Max, I created an Agent Job to do the shrink at 4:15am a few nights ago and it resulted in the same failure "because the logical log file located at the end of the file is in use". I don't know what could be preventing that one VLF from not getting cleared. – Mark Freeman May 8 '14 at 19:15
7

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
  • You cannot do this for transactional replication without disrupting the replication. – Max Vernon May 8 '14 at 17:49
  • 1
    Well, you could always re-initialize replication instead of trying to find some "quiet" window to get away with this. – Aaron Bertrand May 8 '14 at 20:29
  • +1 since you edited your answer to include a way around breaking the replication. @Aaron as always, your suggestion is well worth consideration. – Max Vernon May 8 '14 at 20:47
  • I actually needed a combination of dartonw's sp_repldone call and Max Vernon's "shrink it 1,000 times" script (I used 15 minute intervals) to get the log down to the desired size. – Mark Freeman May 9 '14 at 15:45
  • @Mark, glad you solved the issue. – dartonw May 15 '14 at 2:11
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
  • 1
    I actually needed a combination of dartonw's sp_repldone call and Max Vernon's "shrink it 1,000 times" script (I used 15 minute intervals) to get the log down to the desired size. – Mark Freeman May 9 '14 at 15:46
0

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)

  • I do not understand your supposition. If the database was backed up in the FULL model, then the restore will be FULL as well. Afterward, you can change to SIMPLE, which will allow you to shrink the log files. – RLF Mar 10 '15 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.