Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Help me! The log file for database is full

    Hi,
    Iīm working with a sql server 2000 bd and i have a bd with simple recovery model. Each day i have the next error:

    "The log file for database x is full. Backup the transaction log for the database to free up some log space"

    I tried to limit the transaction log file to 500Mb but then I have this error. I have done the reduction manually of transaction log file but the next day i have got the same error. If i donīt try to limit, this file grows a lot of (1GB) and then i havenīt got enough disk space. Can you help me, please?

    Thanks a lot.
    Memupi

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    I will offer you 2 solutions

    1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

    2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

    exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

    This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

    Hope this answers your question
    Regards
    Dbabren

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    I have tested the value of this option using the following select:
    SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

    and the returned value was '1'.

    Also if i see the options of database i can see that the option "autoshrink" also is set.

    Then, i donīt know what is the problem.

    Thanks a lot.




    Originally posted by dbabren
    I will offer you 2 solutions

    1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

    2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

    exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

    This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

    Hope this answers your question

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

    Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.
    Regards
    Dbabren

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    I have executed loginfo query and the result is only one active segment. But, this segment is the last. Then, the shrink is not effective?? What can i do at this point?

    But if i'd execute a manual command to shrink only the log transaction directly from Sql enterprise, the log file would be shorter. What is the reason? I can test this point.

    On the other hand, i have limited the file to 500Mb. What happens if i execute a big transaction and i donīt have enough space in the log file to save all ? Can i have this problems? When i didnīt limite the space of log file i didnīt have any error (the problem of space disk, of course).


    Originally posted by dbabren
    OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

    Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.

  6. #6
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

    As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space
    Regards
    Dbabren

  7. #7
    Join Date
    Feb 2004
    Posts
    7
    I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

    Can i remove the transaction log or config to not use?



    Originally posted by dbabren
    where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

    As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space

  8. #8
    Join Date
    Feb 2004
    Posts
    7
    Or how can i change the transaction file to another disk? Then i could not limit the log.


    Originally posted by memupi
    I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

    Can i remove the transaction log or config to not use?

  9. #9
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Can't not use a transaction log - not possible

    You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.
    Regards
    Dbabren

  10. #10
    Join Date
    Feb 2004
    Posts
    7
    OK. Thanks for all. You has helped me a lot.

    Originally posted by dbabren
    Can't not use a transaction log - not possible

    You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.

  11. #11
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi

    since ur drive is running out of space.
    u can free up some space in which the log file is already present
    else look out for another which is free of space
    1. create a folder to store logfiles
    2. go to the properties of the job that is taking the backup of log
    3. go to edit and change the location (drive) from previous to present drive where there is enough space

    hope it will work

Posting Permissions

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