Attn OT SQL DBAs - transaction log problem...

Discussion in 'OT Technology' started by Supernaut, Nov 10, 2006.

  1. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    We are running SQL 2005, in an active/passive cluster. Our transaction logs are getting out of hand. The DBs are backed up twice daily, Full recovery model. Running DBCC SHRINKFILE returns Cannot shrink log file 2 (DB_Log) because all logical log files are in use. I'm not a DBA in any sense, but I have to keep the transaction logs in check until we fill the opening. A temporary maintenance plan I created to perform a transaction log backup and shrink completes successfully, but to no effect on the size of the log and no errors in the plan history. :confused:

    DBCC OPENTRAN returns:

    Code:
    Transaction information for database 'DB'.
    
    Replicated Transaction Information:
            Oldest distributed LSN     : (0:0:0)
            Oldest non-distributed LSN : (5145:825:1)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
     
  2. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    I'd try to help but your AV is too annoying
     
  3. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    I doubt you know anything anyways. Next.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    We had the same problem - once we delete the trans logs manually, it seemed to clear up. There's no reason to keep logs prior to the dbs being backed up. Our problem revolved around a point in time where it ran out of disk space.
     
  5. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    :rofl:
    touche'
     
  6. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    It looks like the log reader that's used for replication is not keeping up (Oldest non-distributed LSN : (5145:825:1)). You won't be able to shrink the transaction log past any transactions that have not been marked as passing replication. Are there any publications on this DB?
     
  7. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    Bear with me, as DBA is not a hat I typically wear. What do you mean by publications?
     
  8. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    If the database is being replicated, it will have publications on it. Check Tools -> Replication to see if there are any publications.
     
  9. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    I see. Checked it, no publications.
     
  10. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    It sounds like your DB is not configured properly then. I would guess that the transaction log isn't being truncated because it thinks the log reader for replication needs to use it. If there are no publications though, nothing will ever read it and the log will keep growing even after log backups.

    First verify that this DB is not involved in any replication process. If that's the case, try running:

    exec sp_repldone null, null, 0, 0, 1
    exec sp_dboption 'db_name', 'published', 'false'

    That should mark all transactions in the log as done and allow the log to be truncated.
     
  11. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    What can I do to verify this? Other than it being a cluster, there's no other replication that I'm aware of. Thanks again for all of your help.
     
  12. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Try:
    Code:
    USE <db_name>
    
    SELECT [name], category
    FROM sysobjects
    WHERE type = 'U' AND category & 32 = 32 OR category & 64 = 64
    
    If you get no results, the DB is not involved in any replication. If you do get results, post them.
     
  13. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    No results from that, so I proceeded with....
    That returned:

    Code:
    Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
    Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
    The replication option 'publish' of database 'MyDB' has been set to false.
     
  14. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    Tons. The log volume is on a SAN so I can continue to extend the volume as necessary. However, as our last DBA did not know what he was doing when he set this up, I'm not confident that I can count on our next DBA being competent either. I'd like to get to the bottom of this and know that we won't have this issue in the future. Whatever the problem is, it is effecting test databases copied from the production, even though the test databases are set to Simple recovery model. :eek4:
     
  15. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    What you will have to do is create a temporary publication on the DB. Then re-run those procedures.
     
  16. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    None. Backups are done through Arcserve's SQL Backup agent or run through SMS.
     
  17. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    OK, ran:

    Code:
    exec sp_dboption 'db_name', 'published', 'true'
    exec sp_repldone null, null, 0, 0, 1
    exec sp_dboption 'db_name', 'published', 'false'
    
    ..on a test database. DBCC OPENTRAN shows no active transactions. After a full backup (test database is Simple Recovery model) the transaction log shrank to 1MB. Seems to have fixed the problem for the time being, I guess time will tell if the fix is permanent or not. Are there any implications to doing the above on the production database?
     
  18. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Well, since it's not involved in any replication, it should be fine. If setting the 'published' flag to true doesn't help, you might want to try creating an actual publication (with log reader and distribution agent) and then re-running the sp_repldone procedure. Try it first using the 3 calls you used on the test db though.
     
  19. Supernaut

    Supernaut New Member

    Joined:
    May 20, 2003
    Messages:
    8,047
    Likes Received:
    0
    I did it both ways, on two separate test databases. Seems to worked either way, but removing the snapshot replication was a manual process, didn't care much for that. Seems like setting the published flag to 'true' is enough to trick it in allowing the sp_repldone. I'll try it out on the production database on Friday and report back. Thanks for all of your help. :bowdown:
     

Share This Page