Post reply

Is it Possible to Shrink destination database log file

  • June 30, 2009 at 2:28 pm

    #95552

    I have transactional replication,in which the size of log file of distribution database is very big now

    Is it Possible to Shrink destination database log file?

  • June 30, 2009 at 2:33 pm

    #1017957

    What is the recovery model of the distribution database?

  • June 30, 2009 at 2:48 pm

    #1017968

    Its in Simple mode and the size of data file is 1926MB and the size of log is 86839MB,If I shrink the log file,does this effect on Replication process?

  • June 30, 2009 at 4:36 pm

    #1018051

    Not a replication specialist, but it looks like there are replication transactions that have not yet replicated to all subscribers. How space is bein used in the T-Log?

  • July 1, 2009 at 1:54 am

    #1018207

    I suspect that you had the database in "Full" recovery mode at some point, without a maintenance plan to back up the transaction log, so it will have continued to grow.

    As far as maintenance plans are concerned, the distribution database falls between 2 stools... if you set up a maintenance plan for "system" databases, this doesn't include the distribution database... if you set up a plan for "user" database, this doesn't include it either.

    You can shrink the log without it having any affect on replication.

    You might want to run DBCC SQLPERF(logspace) to confirm that hardly any of the log is being used.

  • July 1, 2009 at 9:44 am

    #1018522

    Ian,

    Thanks for your response.

    I have checked the recovery mode of Distribution database several times, and it is in simple mode.

    My question is that,why transaction log should grow when it's recovery mode is simple?

  • July 1, 2009 at 10:36 am

    #1018571

    Is the log still growing?

    I was suggesting that the log had PREVIOUSLY been in Full recovery (which caused it to grow).

    Although it is now in Simple, that in itself will not shrink the log, but should cause it to stop growing.

    If you run DBCC SQLPERF(logspace), what does that tell you about the amount of space used in the Distribution database log?

  • July 1, 2009 at 1:59 pm

    #1018683

    Yes the log still is growing,although the recovery mode of database is simple.

    I am a little confused,what should I do to prevent the transaction log to grow?

  • July 1, 2009 at 4:24 pm

    #1018777

    Also,When I ran DBCC SQLPERF(logspace),here is the result:

    Database name Log Size(MB) Log space Used(%) Status

    Distribution5856.429791.332291 0

  • July 2, 2009 at 1:39 am

    #1018953

    Distribution 5856.4297 91.332291 0

    At least thats smaller than the 86839MB you originally had.;-)

    You said this in the original post

    Is it Possible to Shrink destination database log file?

    Are we talking about the "destination" database or the "distribution" database?

    Other than that, I can only guess that you have some very large transactions that SQL Server is trying to replicate.

Viewing 10 posts - 1 through 9 (of 9 total)

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