Post reply

Large log file.

  • June 3, 2009 at 1:46 am

    #222026

    I have simple recovery model database.

    Database size is 29GB. But Log size is 88 GB.

    DBCC SQLPERF(LOGSPACE) is showing 97% full

    I ran CHECKPOINT and BACKUP LOG WITH TRUNCATE_ONLY. But no effect.

    DBCC OPENTRAN() is showing followin out put. I guess there is no open transaction.

    Transaction information for database 'xxxx'.

    Replicated Transaction Information:

    Oldest distributed LSN : (0:0:0)

    Oldest non-distributed LSN : (1450439:54013:6)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    The interesting point to note is, this database replicated. In the publisher, the log size is only 5 GB.

    Why is the log file so big in the subscriber? How can I, shrink it?

    Any help will be highly appreciated.

  • June 3, 2009 at 2:34 am

    #1003833

    The open tran indicates that this was once replicated (publisher) or was restored from a backup of a published database. Either way, there's left-over replication that's preventing the log from truncating.

    Create a publication in the DB, publish any table then drop the publication. That should fix the problem for you and OpenTran should have no reference to replicated LSNs afterwards. Then you can shrink the database to a reasonable size.

    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

  • June 3, 2009 at 3:22 am

    #1003858

    Thanks Gail,

    You are a genious!

    Your solution worked.

    Thanks.

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

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