Post reply

Change Recovery Model of Distribution Database

  • April 26, 2012 at 3:52 am

    #257110

    Hi,

    Please could someone give me some help. The transaction log of our distribution database is becoming very large and I am trying to change the recovery model to full so that I can backup and shrink the log file.

    ALTER DATABASE [Distribution] SET RECOVERY FULL WITH NO_WAIT

    But it is failing with the message :

    Msg 3023, Level 16, State 2, Line 1

    Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

    I have run dbcc opentran (distribution) and there are no open transactions.

    When I run

    SELECT name, recovery_model_desc, log_reuse_wait_desc

    FROM sys.databases

    WHERE name = 'Distribution'

    The log reuse wait description is : ACTIVE_BACKUP_OR_RESTORE

    I know I need to get the log_reuse_wait_desc to INACTIVE but don't know how to go about doing it.

    Any help would greatly appreciated.

    Thanks

    Wayne

  • April 26, 2012 at 4:27 am

    #1479558

    Firstly, changing the recovery model to full because the log is large is a inappropriate thing to do. The log gets automatically marked as reusable in simple recovery model, in full though it requires a log backup.

    Hence, by switching to full recovery, you would be making the log harder to reuse and shrink, not easier.

    There's an active backup of distribution, that's why the log is growing. Identify what is backing the database up, stop it, then you will be able to shrink the log to a reasonable size.

    Please read through this - Managing Transaction Logs[/url] and this http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 26, 2012 at 4:27 am

    #1479559

    Don't change the recovery model, change the subscription experation

    see this artical

    http://www.sql-server-performance.com/2011/growth-distribution-database/

  • April 26, 2012 at 4:30 am

    #1479562

    Thank you so much!!! Just what I was after.

    Wayne

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply