SQL Server Performance Forum – Threads Archive

Invalid object name master.dbo.spt_values

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

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

This might help. http://sqlserver2000.databases.aspfaq.com/why-do-i-get-errors-about-master-spt-values.html Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

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

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

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

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

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…

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.

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

]]>