Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    79

    Unanswered: entries in syslogshold

    Hi


    I had a problem in one of my user databases.
    (I am using sybase 12.5.0.3 on NT)

    When I looked at logsegment for my userdb, its was getting full though truncate log on checkpoint is turned on. When I saw syslogshold table, I could see one topen transaction in the database and corresponding process was in sleeping mode. So when I tried to kill that process, I wasn't able to do so b'coz process was sleeping. I tried to reboot the sybase server, but then database cudn't recover as I got following message in errorlog:

    00:00000:00001:2004/05/24 01:10:44.96 server Recovering database 'myDB'.
    00:00000:00001:2004/05/24 01:10:44.99 server Redo pass of recovery has processed 42 committed and 0 aborted transactions.
    00:00000:00001:2004/05/24 01:10:45.02 server Error: 1105, Severity: 17, State: 3
    00:00000:00001:2004/05/24 01:10:45.02 server Can't allocate space for object 'syslogs' in database 'myDB' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment.
    00:00000:00001:2004/05/24 01:10:45.02 server Error: 3475, Severity: 21, State: 7
    00:00000:00001:2004/05/24 01:10:45.02 server There is no space available in SYSLOGS for process 1 to log a record for which space has been reserved. This process will retry at intervals of one minute. The internal error number is -4.

    I cudn't increase the size of logsegment since database was in recovery mode.Then I have to mark database as suspect, drop it and recreate it as it was one of the smallest database I have.

    The resolution I did, was not perfect so I would like to know if I could have followed a simpler approach than this, like killing spid which is responsible for all this mess and how can I find root cause of this open transaction (definately there is no major updates on sundays)

    spid kpid enginenum status suid hostname program_name hostprocess cmd cpu physical_io memusage blocked dbid uid gid tran_name time_blocked network_pktsz fid execlass priority affinity id stmtnum linenum origsuid block_xloid clientname clienthostname clientapplname sys_id ses_id loggedindatetime ipaddr
    ----------- ----------- ----------- ------ ----------- -------- ------------ ----------- --- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------- ------------ ------------- ----------- -------- -------- -------- ----------- ----------- ----------- ----------- ----------- ---------- -------------- -------------- ----------- ----------- ---------------- ------
    391 962068714 3 sleeping 67 myAppn myDB 1812 UPDATE 1 2 4 0 6 3 16391 $upd NULL 2560 0 EC2 MEDIUM ANYENGINE 0 1 1 NULL 0 0 0 May 17 2004 3:16AM xxx.xxx.xxx.xxx

    I will appreciate sybase guru's comments.

    Rgds

    Wilson

  2. #2
    Join Date
    Nov 2003
    Posts
    30
    if a process is in log suspend state you can abort the process by lct_admin. If that approach do not succeed you have to recycle the server after marking the affected database as suspect. After that you need to run the dump tran command to free up the log..

  3. #3
    Join Date
    Apr 2004
    Posts
    79
    I did the same thing as you mentioned.
    I cudn't abort the process using lct_admin as process was sleeping. Then I have to mark the database suspect, recycle sybase server, and then since my userdb was very very small, and I had an upto date dump, so I dropped the db and recreated that as I was not sure whether It will be helpful to change the db status from suspect to normal.(which should work when I gave it a after thought). Also we cant take tran dump unless db status is changed to normal from suspect.

    Thx for ur response.

    Rgds

    Wilson

  4. #4
    Join Date
    Nov 2003
    Posts
    30
    if the database is in suspect state also you can free up the log space by dump tran ... with nolog

  5. #5
    Join Date
    Apr 2004
    Posts
    79
    Is it...!!!!!

    Its news to me...thanks!!!

Posting Permissions

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