SQL Server Performance

Invalid object name master.dbo.spt_values

Discussion in 'SQL Server 2005 General Developer Questions' started by ashish.johri2007, Mar 1, 2007.

  1. ashish.johri2007 New Member

    Hi,
    Here is one error as below while I try to see the properties of one DB.
    “Can#%92t show requested log. Invalid object name master.dbo.spt_values.”

    This is not master DB then why master.dbo.spt_values? I am unable to make out what the problem is. Anyway y#%92day there was one error in execution and so I have to kill the spid and then it showed “Recovering DB”. And now this displays the below problem.

    Also I tried to delete this DB. But it is unable to delete. This shows error 3702. and shows that the DB is in use.
    Any pointer who can address this issue.

    Regards,
    Ashish



    Ashish Johri
  2. madhuottapalam New Member

    many system stored procedures are using spt_values (there are 5 spt_ prefixed tables used in many system stored procedure as various lookup tables) as lookup table. these are in master database. It can get corrupted(even get deleted). if you run 'sp_helptext sp_helpdb' you can see how this table is used). Check this table is there in the master database or not. very few documentation are available regarding these tables. I had similar problem and i have posted it some forum, iam not able to find it now.. i will come back..

    Ref :http://www.sql.co.il/ug/06/secrets.ppt

    Madhu
  3. Roji. P. Thomas New Member

  4. madhuottapalam New Member

    if Droping database is your problem then see if there is any process accessing the DB by running SP_WHO2 kill that process and drop the database.

    Madhu
  5. ashish.johri2007 New Member

    Hi this is correct. Master DB was corrupted. Can anybody tell me the rudimentary reasons why this gets corrupted?

    Ashish Johri
  6. ashish.johri2007 New Member

    Y'day I ran some DBCC commands on this DB. Is this the reason that master DB got corrupted? Pls any pointers....



    Ashish Johri
  7. madhuottapalam New Member

    i have experienced this couple of time..but i could not recreate the problem to report Microsoft. Since yours is a fresh case i think we need to Analyse the steps/process you had taken before the corruption of master db. pse explain step by step

    Madhu
  8. ashish.johri2007 New Member

    I gave command as below:
    1. DBCC ERRORLOG – This is to remove error log in the DB
    2. DBCC FLUSHPROCINDB – To flush cache memory for SPs
    3. DBCC INDEXDEFRAG – To defragment the data in the table.


    Ashish Johri

    Ashish,
    To find the corruption... run the DBCC CHECKDB not the above mentioned procedures...
    Run the DBCC CHECKDB and post the results so that poster can answer better...

    DBCC ERRORLOG recycles the sql errorlogs which it will remove the first log from the last and create new one...
  9. satya Moderator

    Have you looked at Roji's reference?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. madhuottapalam New Member

    Ashish... is the problem sorted out... whole master db got corrupted or only these tables...

    Madhu

Share This Page