Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Unanswered: log filling up to hundreds of megs for tiny app!!!

    I don't understand this at all!! I cannot add anything to the database as it says:

    Can't allocate space for object 'syslogs' in database <my DB> because 'logsegment' segment is full/has no free extents.

    I have used ALTER DATABASE to add a new device for the logsegment, 5 times!! @ 20Meg a pop. What is going on?? It seems that when I add the new space, the logsegment gets filled as soon as I insert new data into the DB.....No matter HOW much I extend the segment. This is a tiny app. I just don't understand enough about Databases - can someone please tell me what I have to do?

    I can't dump transaction either, because it says I must dump database first. I don't get it. All I want is for my app to function, without filling up the DB.

    Thanks,
    Anxious

  2. #2
    Join Date
    Jul 2003
    Posts
    9
    I have narrowed this problem down to a trigger...

    For some reason - ANYTIME I try and access the inserted table in my Insert trigger, the transaction log just fills up until it runs out of space. I have performed simple queries in my trigger (even something as simple as select * from inserted) just to see why it locks up on me. It doesn't lock up and fill the tran log when I do a query that DOESN'T access the inserted (or deleted) table. This is very strange. Can someone please help me? I have no other triggers working...

    Thanks...

  3. #3
    Join Date
    Sep 2003
    Posts
    1
    You need to carry out a full database dump before you can do a txn log dump. If you don't need to backup this database (?) then just set the db_option "trunc log on chkpt" - thisd will clear down the txn log every time a checkpoint occurs i.e. fairly regulalry

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The transaction log will keep filling up until you clear it, either with a DUMP TRANSACTION command or setting the dboption to trunc log on checkpt as was previously noted.

    Also you might want to set abort tran on log full as well.
    Thanks,

    Matt

  5. #5
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26

    Red face

    Hi,

    i've also have this problem, but not the solution.

    Have you find a solution ?

    Carbone.

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Try

    Code:
    dump tran YourDB with no_log

  7. #7
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26

    Thumbs down

    Sorry, i'm not enough clear :
    i have THIS problem : i run an insert (only one record) on a table with a "minimal" trigger.
    On "SELECT @ins_row = COUNT(*) FROM inserted", ASE fills up the log segment (who were empty before !)

    I've find some post on the web like this, but no answer.

  8. #8
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Sorry for the missunderstood. Can you please provide us
    1) an exact repro
    2) the output of select @@version

  9. #9
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26

    Arrow

    - the version : Adaptive Server Enterprise/12.0/P/SWR 8776 ESD 1/NT (IX86)/OS 4.0/1580/32bit/OPT/Mon Dec 06 21:50:07 1999

    - the insert statement :
    insert into INLAMA (CDINEL,COOC,COPAIP,CORAIN,COSI,COSU,DCRE,DMAJ,IND EMG,INDEMP,INDETB,INDETP,INEL,INLAIT,INREFI,NBCFLA ,NBFILA,NULITR,NUNATI,PCLADE,PCPRLA,REFIND)
    values (72,"IEIN","FR","46","M","0","Jun 12 2003 12:00AM","Oct 15 2003 10:25AM",41,37,16,10,23,10,9,47,48,"001621","01970 00633",7,94,"0340")

    - the trigger :
    Create Trigger TR_INLAMA
    On INLAMA
    For Insert,Update
    As
    Begin

    BEGIN /* Traitement */
    SELECT @ins_row = COUNT(*) FROM inserted
    SELECT @del_row = COUNT(*) FROM deleted
    END /* FIN DE TRAITEMENT */

    END

    - the error : classic error log full (Can't allocate space for object "syslogs" ....)

  10. #10
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) The DDL (CREATE TABLE) is missing
    2) what's the goal of your trigger ??? What do you do with @ins_row and @del_row ???

  11. #11
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26
    Hi,

    This trigger has no goal, first it contains a lot of code, but only this code is enough to cause the problem.
    I'd find the solution :
    there is in this version a bug (216805). When you alter the table, and modify the trigger, it can append this problem.
    Recreate the table and load datas solve the problem.
    This bug were fixed in 12.0.1.

    Thank's a lot,
    Carbone.

Posting Permissions

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