Blogs from Suhas

Dirtying my hands in SQL Server

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

  • Comments 27

Hi Friends,

The last month has been a very busy month for all of us, and its not over yet. However, with the daily work, comes a few instances when we do encounter really interesting issues. End of the last month, I encountered an issue, that is really very interesting.

The issue that I am talking about is very uncommon; I have not seen many people facing it; although it is very easy to encounter this scenario. Also, this issue can be encountered in SQL Server 2005; although I have not tested this against SQL Server 2008.

I am sure all of us are aware that a system database called master exists and is the main database that is required for startup of the SQL Server. If we look into the master database, in SQL Server 2005, we would see that there are 5-6 system tables (some systems might have 5; some systems might have 6). But are we aware of what these tables contain, and what if these tables go missing?

You might be thinking - "What! Missing system tables; that too from the master database!" Believe me, its not very uncommon to have the system tables going missing. Right-Click on any of these tables, and choose delete... That's all about it...

Note: However, even after having a thorough understanding of this blogpost, I would recommend all users NOT to try this on any SQL Server instance...

There are several reasons why all or some of the system tables might go missing. An improper database upgrade from SQL Server 2000 can cause some or all of these tables go missing. Also, user activity (right-click and delete) can cause these tables go missing. In case its user activity that deleted these tables, an inspection of the C2 Audit Traces can reveal who did that and when. The default traces also can reveal this information; however, since the default traces are deleted periodically, there is a high probability that the information might get lost after a certain interval of time.

Now, the question remain is - what happens if these system tables go missing? Nothing much, only problem that will arise out of the missing dbo.spt_values table is that you can no longer right-click database objects and look at their properties. for example, in case you right-click database objects and choose properties, you would face an error message saying:

Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

However, in case these system tables go missing, and you encounter the error message as mentioned above, don't panic; it can be repaired. In the <SQL Server Install Dir>\MSSQL\Install folder, there is a script file named u_tables.sql. Execute this script against the instance that has these tables missing; and there you go. The dbo.spt_monitor and dbo.spt_values table will get created and appropriate data will be filled in, there by the error message mentioned above will go away.

There is no default script to create the other 3-4 tables; however, these can be scripted out from any other instance and the script can be applied to create the rest of the missing tables. One of these table is a replication related table, that is supposed to have 3 rows of data; this data can also be imported from a good instance.

Hope you find this post useful!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments
  • PingBack from http://patiochairsite.info/story.php?id=30262

  • This will become very usefull for me.

    Thanks for his topic.

    Anil Patel

    INDIA

  • Hi Anil,

    Thanks for the comments. Glad to be able to help.

    Regards,

    Suhas

  • Thanks for your explanation. It was exactly my problem, and I've solved it as you explain.

    Thank you very much.

  • And your solution works in same way (well, in fact, u_tables.sql was in cd not in any hard disk folder).

  • Seems that 'Title' is not posted: I want to say that my problem was in SQL Server 2008 and your solution works fine too.

  • Hi Cx,

    Thanks for the comments! It's good to know that my efforts have been useful to you.

    The u_tables.sql script file should be located on the hard disk. On my machine it is located at "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install". Not sure why this does not exist on your disk.

    Thanks, Suhas

  • thank you. saved me a heap of time!

  • Very useful this article and helped save time.

  • This will become very usefull for me when i configure replication.

    Thanks for this topic.

    Sunil Botadara

    INDIA

  • really thank you for your post!!!!!!!

    i have been hard rime for more than 48 hours~

  • That fixed it with no trouble.  Thank you!

  • I was facing the exact problem....and got resolved with this solution.... Thanks a lot for this posting

  • Excellent!!!. Even , I had the same problem.It has really fixed my problem. Good post buddy...

  • Thanks, it helped me.

Page 1 of 2 (27 items) 12
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post