Post reply

Internal Table

  • January 16, 2009 at 4:58 am

    #127054

    Hi All

    For the last 3 days i am having a problem with Log Shipping, when a Log file is been restored database goes into suspect, this happens, when there is a log which is been generated after a archive job each morning ( on other words, the log generated after archive job is failing), my database goes to suspect mode, when looked at the error code i found from MS that

    A database is marked as suspect when you update a table that contains a nonclustered index in SQL Server 2005

    Check on this link http://support.microsoft.com/kb/934734

    They have got a CU, but i already have SP2 on my 2005 (64 bit edition). MS advised me a workaround by enabling Allow_Page_lock, i checked my database for tables that have this option disabled by using

    select * from sys.indexes where index_id = 0 and allow_page_locks = 0

    This came out that i was having this on Internal Table.

    My Question is not, can i update this internal table index to allow page locks, or can i delete the Internal table, or is there any alternative ways to do this, have any one faced some problems like this before ??

    Thanks in Advance

    Cheers

    🙂

  • January 16, 2009 at 5:25 am

    #927973

    What version of SQL are you on? (SELECT @@version)

    What do you mean by 'internal table'? What's the name of the table that you think is responsible?

    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

  • January 16, 2009 at 5:32 am

    #927984

    Hi Gial

    Thanks, This is the version below.

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    To be honest i am hearing this term for the first time, its explained on MS website

    http://msdn.microsoft.com/en-us/library/ms366343.aspx

    I can get the table information by using sysobjects table with Type='IT'

    Hope this explains. 🙂

  • January 16, 2009 at 6:12 am

    #928013

    Ah, those.

    You still didn't answer my question. What's the name of the table that you think is causing the error?

    Have you considered apply Cumulative update 2 (or higher) as, according to the kb article, that should fix the problem. As of now, there are 11 cumulative updates for service pack 2, or you can just apply SP3, which contains up to CU 9

    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

  • January 16, 2009 at 6:33 am

    #928041

    Ops Sorry Gial, the name of the table on sysobjects table is "queue_messages_1003202674" , i will try to update SP3 and see how it works:-)

    When i tried to apply the CU that MS mentioned, it said that an advanced version is been installed on the machine 🙂

    Cheers

    🙂

  • January 16, 2009 at 6:46 am

    #928064

    CrazyMan (1/16/2009)

    When i tried to apply the CU that MS mentioned, it said that an advanced version is been installed on the machine 🙂

    It's one of the service broker queues. Not something you're going to be able to change.

    From the version no, you have SP2 straight, with no cumulative updates at all

    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

  • January 16, 2009 at 10:54 am

    #928353

    Thanks Gial

    I have installed SP3 on the problem server, let me wait and see what happens and provide a update after week end

    Cheers 🙂

  • January 19, 2009 at 2:58 am

    #929069

    HI Gial

    Thanks, i have installed SP3 on Friday, this seems to have fixed the problem. nothing until now 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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