Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Unanswered: How to close open tran from replication cause log space not free

    Dear Experts,

    Someone accidentially bcp in a huge amount of records to primary database without -b option. This fed up all syslogs in replicated databases.
    I've truncated the bcp in table in primary db.
    Then I purged the replication queue and restarted replication server.
    I dump database and tran many times and cannot free the filled syslog on replicated db.
    No matter the replication server is on or off, I found 1 record in syslogshold which belongs to the replicate process and of spid 0.
    How to close this open tran to free the syslog ?

    Please help !

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    What is on syslogshold?

    If its the replication maint user, you should have killed it before flushing the queue etc.... If you kill it now and resume the dsi thread, hopefully it should work. You can also confirm what its doing by

    dbcc traceon(3604)
    go
    dbcc sqltext(spid)
    go

    OR
    sp_showplan spid,null,null,null
    go

    If its something else, you need to figure out what it is etc...

  3. #3
    Join Date
    Jul 2007
    Posts
    7
    There is only 1 tx in syslogshold:
    1> select * from syslogshold
    2> go
    dbid reserved spid page xactid masterxactid
    starttime
    name
    ------ ----------- ------ ----------- -------------- --------------
    --------------------------
    -------------------------------------------------------------------
    6 0 0 93459 0x000000000000 0x000000000000
    Jan 1 1900 12:00AM
    $replication_truncation_point

    I guess spid 0 is not allow to be killed , right ?

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Tiffany Fong
    There is only 1 tx in syslogshold:
    1> select * from syslogshold
    2> go
    dbid reserved spid page xactid masterxactid
    starttime
    name
    6 0 0 93459 0x000000000000 0x000000000000
    Jan 1 1900 12:00AM
    $replication_truncation_point

    I guess spid 0 is not allow to be killed , right ?
    Thats the replication agent process. Are you sure this is the replicate. If it is so, that means theres nothing holding the transaction log, except maybe a truncation marker from a load may be which you forgot to remove if its ONE way replication.

    Or its one of the following setup (warm standby/bi-directional). If I get it right, a starttime of null means the agent is not running, which most probably means its a warm standby.

    The info you have provided is a bit not complete for me to actually advice you. However, if you can paste the following, maybe I can try to see whats going on..

    Do the following.

    1. Find the database name for id 6.
    select dbid,name from sysdatabases where dbid=6

    2. From output of 1.
    use dbname
    go
    dbcc gettrunc
    go
    sp_help_rep_agent <dbname>
    go

    3. Log into Rep Server and run the following
    admin who
    go
    admin logical_status
    go

  5. #5
    Join Date
    Jul 2007
    Posts
    7
    What is a "truncation marker" ? Under what situation will it exist ? How to remove it ?
    What does "dbcc gettrunc" do ?

    You are right. The rep_agent is not running for db_id 6 which is a user data database. I've restarted it around 3 times before successfully bring it up. After it is up, the start time of the process in sysprocesses becomes current time. I've dumped the database and transaction log around 2 times. Then the log starts to release space ! The log is totally empty now. Thank you for your hints !

    Following on, more interesting problems coming up -> isql cannot login RS. Then I found that RS's own database, RSSD, has the same log full problem. I've repeated above steps and does not work. Then I started up another replicated database and its rep_agent. I altered 20M to the log and dump the db and tx. Then the RSSD log started emptying. And finally free up all log space. Everything seems normal now. I should celebrate at this point of time. ......but a little while later, the RS core dump and the RS instance extinguished ... I cannot understand why "purge queue" cause a series of difficult problems ? Is there some more special commands I needed to issue together with "purge queue" ? (the reference manual never mentioned additional steps)

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Tiffany Fong
    What is a "truncation marker" ? Under what situation will it exist ? How to remove it ?
    What does "dbcc gettrunc" do ?

    You are right. The rep_agent is not running for db_id 6 which is a user data database. I've restarted it around 3 times before successfully bring it up. After it is up, the start time of the process in sysprocesses becomes current time. I've dumped the database and transaction log around 2 times. Then the log starts to release space ! The log is totally empty now. Thank you for your hints !

    Following on, more interesting problems coming up -> isql cannot login RS. Then I found that RS's own database, RSSD, has the same log full problem. I've repeated above steps and does not work. Then I started up another replicated database and its rep_agent. I altered 20M to the log and dump the db and tx. Then the RSSD log started emptying. And finally free up all log space. Everything seems normal now. I should celebrate at this point of time. ......but a little while later, the RS core dump and the RS instance extinguished ... I cannot understand why "purge queue" cause a series of difficult problems ? Is there some more special commands I needed to issue together with "purge queue" ? (the reference manual never mentioned additional steps)
    Glad you sorted out the replicate side. So, Im guessing its bi-directional.

    An easy way to check if anything has gone wrong is by running

    admin who_is_down
    go

    in the RepServer


    What did you do when RSSD got full? I hope you didnt truncate the log by ignoring the marker or anything.

    purge queue is not a normal command and unfortunately not sure if I can walk you thro everything on this thread because the order in which you do certain things in RepServer is important. By any chance do you have a support contract with Sybase? Its probably easier that way.

    If you dont, and you are prepared to wait, then you have to post the errorlog details when you try to start the repserver and also tell us what you did with the RSSD when it got full. I hope you extended it or just started the repagent.

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Also do post the output of the previous commands I mentioned. I can assure you none of the commands I suggested is in anyway intrusive. It will just give me an idea of your environment a bit more.

  8. #8
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Tiffany Fong
    What is a "truncation marker" ? Under what situation will it exist ? How to remove it ?
    What does "dbcc gettrunc" do ?
    Sorry forgot to answer your other questions.

    In simple words, when you say a database is a primary in Replication Server, it puts a second marker which is what a truncation marker is. A VALID truncation marker will exist for every primary database. So, if it is a valid primary and the replication agent is down, no matter how many times you truncate the log using dump tran, it will not truncate it because of the secondary marker.

    It will exist in two conditions. One, when you have actually defined it and its supposed to be there. Two, when you have loaded this particular database from a replicated environment, the marker will still carry forward. So, if this database is NOT meant to be replicated, then you need to remove the marker.

    dbcc gettrunc will tell you the gen_id and whether the marker is valid or not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •