Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)
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
Anonymous
June 02, 2009
PingBack from http://patiochairsite.info/story.php?id=30262Anonymous
August 09, 2009
This will become very usefull for me. Thanks for his topic. Anil Patel INDIAAnonymous
August 09, 2009
Hi Anil, Thanks for the comments. Glad to be able to help. Regards, SuhasAnonymous
September 07, 2009
Thanks for your explanation. It was exactly my problem, and I've solved it as you explain. Thank you very much.Anonymous
September 07, 2009
And your solution works in same way (well, in fact, u_tables.sql was in cd not in any hard disk folder).Anonymous
September 07, 2009
Seems that 'Title' is not posted: I want to say that my problem was in SQL Server 2008 and your solution works fine too.Anonymous
September 07, 2009
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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLInstall". Not sure why this does not exist on your disk. Thanks, SuhasAnonymous
October 01, 2009
thank you. saved me a heap of time!Anonymous
October 06, 2009
Very useful this article and helped save time.Anonymous
December 23, 2009
This will become very usefull for me when i configure replication. Thanks for this topic. Sunil Botadara INDIAAnonymous
January 20, 2010
really thank you for your post!!!!!!! i have been hard rime for more than 48 hours~Anonymous
February 26, 2010
That fixed it with no trouble. Thank you!Anonymous
May 12, 2010
I was facing the exact problem....and got resolved with this solution.... Thanks a lot for this postingAnonymous
June 25, 2010
Excellent!!!. Even , I had the same problem.It has really fixed my problem. Good post buddy...Anonymous
June 29, 2010
Thanks, it helped me.Anonymous
November 23, 2010
Thanks..really helpful document!!!!!!!Anonymous
January 05, 2011
You saved my day. ThanksAnonymous
August 15, 2011
This came in handy today! Thank you!Anonymous
October 04, 2011
The comment has been removedAnonymous
January 19, 2012
Thanks for this post. This worked well for me.Anonymous
April 05, 2012
Thanks a lot for this post. This fixed the problem. Although I don't seem to think the real reason behind this can be an improper upgrade or a user-activity ( atleast in my case its not for sure).Anonymous
November 20, 2012
Thank you Suhas, this definitely helped me.Anonymous
November 24, 2013
I was facing same problem in .2012 I have resolved using this tips. Thank youAnonymous
December 20, 2013
This does not work on SQL2012. I have the u_tables.sql script but running it does not fix the issue (in fact it also complains that spt_values does not exist).Anonymous
March 20, 2014
You have the process of exporting and importing tables spt? Thanks for you Reply.Anonymous
July 01, 2014
@ Steve (20 Dec 2013) Were you able to resolve this? I am having the same issue in SQL2014.Anonymous
July 02, 2014
This is really awesome Man...got resolved using your suggestions Keep going.... My wishes to youAnonymous
May 08, 2015
I never came across this issue but I am happy to be familiar with this issue and resolution. Thanks much !!Anonymous
September 01, 2015
Thanks for sharing. Awestuck, DanielAnonymous
November 24, 2015
Thanks a lot! I was looking for this fix for a long time, it helped me a lot.