Post reply

The transaction log for database is full

  • February 23, 2009 at 12:56 pm

    #155316

    I have a maintenance plan on one of my Prod server and it fails somedays

    and works some days(random)

    when it fails i get this error message

    Executing the query "ALTER INDEX [XPK_database_index] ON

    [dbo].[transactions] REBUILD WITH (

    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

    " failed with the following error:

    "The transaction log for database 'test' is full.

    To find out why space in the log cannot be reused,

    see the log_reuse_wait_desc column in sys.databases

    The statement has been terminated.".

    Possible failure reasons: Problems with the query,

    "ResultSet" property not set correctly, parameters not

    set correctly, or connection not established correctly.

    i checked in sys.databases and it says nothing = Currently there are one

    or more reusable virtual log files.

    this is on SQL 2005 sp2 server and log file is 5 GB with

    autogrowrth =by 500 MB,restricted to 6000 MB

    I cant make it unrestricted which might cause this

    so with these options how to get rid off this things?

    Thanks

  • February 23, 2009 at 1:00 pm

    #948901

    what does

    exec sp_spaceused 'transactions'

    produce

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 23, 2009 at 1:04 pm

    #948904

    right now i m getting this result after executing the command

    Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62

    The object 'transactions' does not exist in database 'cdr' or is invalid for this operation.

    But this error comes while my maintenance plan runs at mid-night..

  • February 23, 2009 at 1:05 pm

    #948906

    It sounds to me like the issue is that the TX LOG is growing twice to its limit of 6000MB and then needing to grow again during the Rebuild and failing at that point. It is probably intermittent because you only have occasions where there log is not able to re-use VLF's.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 23, 2009 at 1:07 pm

    #948907

    hmm may be typing mistake.. i m getting this

    name rows reserved data index_size unused

    transactions2354421 516240 KB511584 KB4312 KB 344 KB

  • February 23, 2009 at 1:27 pm

    #948929

    Here a couple of links that show how to get more information about your TX Log:

    http://jmkehayias.blogspot.com/2008/11/database-transaction-log-part-2-how.html

    http://sqlblog.com/blogs/kevin_kline/archive/2008/11/05/better-ways-to-get-transaction-log-information.aspx

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 23, 2009 at 1:35 pm

    #948940

    hmm, not what i expected to see. When i carry out index maintenance i always set the recovery to bulk logged and then switch back to full afterwards. Alter and create index are minimally logged during bulk logged mode

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 23, 2009 at 1:37 pm

    #948944

    select name,log_reuse_wait_desc from sys.databases

    is it possible to change log_reuse_wait_desc column value from nothing to LOG_Backup?

    because ad hoc updates to system tables are not allowed in sql 2005

    changing this option might solve the issues..

  • February 23, 2009 at 1:44 pm

    #948951

    http://www.sqlservercentral.com/Contributions/Edit/30026

  • February 23, 2009 at 1:46 pm

    #948952

    the index rebuild is bugging out before finishing because it cant complete the task i would think.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 23, 2009 at 1:49 pm

    #948955

    dallas13 (2/23/2009)

    is it possible to change log_reuse_wait_desc column value from nothing to LOG_Backup?

    That view is there to tell you why the log isn't been reused. It doesn't control the log's reuse.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 23, 2009 at 2:45 pm

    #949005

    The question in my mind is - why limit the T-log size? If you happen to have a large table, then that alone could be killing your t-log space.

    Also - have you attempted to do a BACKUP LOG operation on that DB before the index rebuild? It would allow you to free up some space in there first.......

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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