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.