Transaction log full error

  • The database is in simple mode and when I try to shrink the log

    USE db1


    GO


    DBCC SHRINKFILE('db1_log', 20

    )
    GO

    The transaction log for database db1 is full due to 'REPLICATION'

    We have never used replication.

  • Check this, are all zeros?

    select
    name,
    is_published,
    is_subscribed,
    is_merge_published,

    is_distributor

    from sys.databases

  • SQL Guy 1 - Wednesday, August 9, 2017 12:13 PM

    Check this, are all zeros?

    select
    name,
    is_published,
    is_subscribed,
    is_merge_published,

    is_distributor

    from sys.databases

    Check the flag for CDC as well so add:  is_cdc_enabled

    Sue

  • Sue_H - Wednesday, August 9, 2017 12:22 PM

    SQL Guy 1 - Wednesday, August 9, 2017 12:13 PM

    Check this, are all zeros?

    select
    name,
    is_published,
    is_subscribed,
    is_merge_published,

    is_distributor

    from sys.databases

    Check the flag for CDC as well so add:  is_cdc_enabled

    Sue

    Yes it does and I trued to disable the CDC I get the following error

    Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 8]

    Could not update the metadata that indicates database db1 is not enabled for Change Data Capture. The failure occurred when executing the command '(null)'. The error returned was 9002: 'The transaction log for database 'client_705' is full due to 'REPLICATION'.'. Use the action and error to determine the cause of the failure and resubmit the request.

  • Guras - Wednesday, August 9, 2017 12:58 PM

    Yes it does and I trued to disable the CDC I get the following error

    Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 8]

    Could not update the metadata that indicates database db1 is not enabled for Change Data Capture. The failure occurred when executing the command '(null)'. The error returned was 9002: 'The transaction log for database 'client_705' is full due to 'REPLICATION'.'. Use the action and error to determine the cause of the failure and resubmit the request.

    Error 9002 is due to the log being full. It's CDC but reporting as REPLICATION that is filling the log.
    There are ways to manually remove CDC but that's not going to do you any good since it will log those changes - same reason you got the error. 
    Grow the log a bit and disable CDC. If there is absolutely no space on the disk then try moving files off that disk. And if that still is not an option, you can try adding another log file on a different drive and then going back and removing it after everything is fixed. It needs space in the log to disable CDC.

    Sue

  • Before you disable CDC, find out why it's enabled. It may have a legit and important business use.
    Check that the jobs related to CDC are running correctly.

    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
  • There isn't a way you can move a log file without SQL service restart . As mentioned by Gail , there must be some business reason for CDC to be working, as its not a minor decision. Service  Delivery team has to be involved to know better.

    Arshad

  • Arsh - Sunday, August 13, 2017 4:58 AM

    There isn't a way you can move a log file without SQL service restart...

    Just to clarify in case someone runs across this thread, that's not true. You would need to take the database in question offline (or detach/attach), but you wouldn't have to stop/start the entire instance.

    Cheers!

  • What's I said is in the context of a service interruption.

  • Arsh - Tuesday, August 15, 2017 12:50 AM

    What's I said is in the context of a service interruption.

    It's still untrue. You need to stop/start the instance to move files for the system databases, but it's not needed for the user databases as they can be taken offline.
    It's also irrelevant, as no one asked or suggested moving the DB's log file.

    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

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

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