Post reply

Database Log File full

  • August 28, 2003 at 5:53 pm

    #160007

    Howdy

    I received a sql server alert

    DESCRIPTION: Error: 9002, Severity: 17, State: 6

    The log file for database ### is full. Back up the transaction log for the database to free up some log space.

    The confusing thing is that there was still plenty of disk space left on the drive at least 5 gig. The file is set for unrestricted growth and to grow by 10%.

    I am running transaction log backups every 10 minutes and DBCC SHRINKFILE.

  • August 28, 2003 at 6:02 pm

    #471895

    Try DBCC OPENTRAN, see BOL 2000.

    Do you run replication on the database

  • August 28, 2003 at 6:22 pm

    #471897

    No replication

    No active open transactions.

  • August 28, 2003 at 7:42 pm

    #471899

    I have seen this every once in a while on our larger databases. It appears that the log will only autoextend about 255 times during a single transaction before giving this message. I have never been able to satisfactorly reproduce it for Microsoft, so the cause is just my observation/guess as to what is happening on my system.

  • August 28, 2003 at 8:16 pm

    #471901

    Can you Restart SQL and check if problem 'go away'. Another person suggested detach/attach

    Articles(bugs) at http://support.microsoft.com/?kbid=814579 and http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375

  • August 28, 2003 at 9:52 pm

    #471911

    Just found this article http://support.microsoft.com/default.aspx?scid=kb;en-us;282286

  • August 29, 2003 at 1:18 am

    #471929

    Set the option 'Auto Shrink' on this database; it will aso shrink the logfile after it is backed up.

  • September 3, 2003 at 10:58 am

    #472399

    set the auto shrink option in the database

    raghu


    cynosure

  • September 4, 2003 at 6:02 am

    #472525

    You will sometimes get this error when your disk can not allocate growth space fast enough. A lot of times you will get this error from tempdb where one transaction will fail due to this error then the next one will succeed as it has had time to grow, increasing the growth size may help. If the growth is your problem, auto shrink will not help you.

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 4, 2003 at 10:52 am

    #472619

    quote:

    The confusing thing is that there was still plenty of disk space left on the drive at least 5 gig. The file is set for unrestricted growth and to grow by 10%.

    What was the size of the transaction log at the time of failure? Let's say it got to 51 GB. 10% of that is 5.1 GB and you only have 5 GB available.

    -SQLBill

  • September 4, 2003 at 11:17 am

    #472628

    Good call SQLBill, missed the forest for the trees

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 4, 2003 at 9:45 pm

    #472718

    The size of the transaction log was 3 gig.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply