Hi everyone,

As of today, we are unable to connect to the SQL Server connection with
Enterprise Manager. The operation fails with the following error message:

A connection could not be established to DBCLUSTER01

Reason: Invalid object name 'master..spt_values'.
Invalid object name 'master..spt_values'.
Invalid object name 'master..spt_values'..

Anyone know what this means? This is using SQL Server 2000 on a Windows
2000 Advanced Server 2-node cluster. Everything was working fine until
today. The database server seems to be working fine as our web sites are
still able to query the databases. The only thing I found on google
mentions that the master database might be corrupted, but DBCC CHECKDB on
the master database doesn't find any errors.

Any help would be appreciated.

Thanks.

Hello -
The error message indicates some corruption in the master database.
Check for the table spt_values and see whether it is present in your master
database. If it is not, then somehow it got dropped and that is quite
harmful.
There are scripts to recreate those system tables but I would recommend you
take the standard way of going for rebuilding and restoring the master
database.

Regards,
Chandra

--------------------
phx.gbl!tk2msftngp13.phx.gbl

Yes, I think someone here messed up while creating a database because I
found some user tables in the master database, but he won't admit it...
Anyhow, I used the rebuildm.exe utility that is on the sql server 2000 cdrom
to recover, but I had to recreate the databases and logins...

"Chandrasekar Gopalan" <chandra (AT) online (DOT) microsoft.com> wrote

Two options for next time
1) To keep all logins and databases and databases intact- lock out all users an back up all DBs first. Restore master from backup using BOL topic "Restoring the master Database from a Current Backup." Basically start SQL Server in single-user mode from the command prompt(sqlservr.exe -c -m). Then open EM and restore master. Once master is restored, restart SQL Server normally. In EM you will see that every database is suspect. That means you will now have to individually restore all databases. Logins stay in tact as well as databases and their users

2)Rebuild master the way you did, add logins that were available previously, restore DBs from backup, See "Troubleshooting Orphaned Users" in BOL. I usually just drop all users from the DBS and re-add them to the DBs via the login. Any time you have a different master than was used at the time database users were added to the database, there is a possibility for orphaned users

hope this help

When you restore master from backup, why do you have to restore all
databases again ?Why would the dbs go suspect ? We didnt rebuild master..
And even if they do go suspect after restoring master, can we just not reset
the suspect status ? I havent played around but just asking


"Doug Guerena" <anonymous (AT) discussions (DOT) microsoft.com> wrote

"Restoring the master Database from a Current Backup." Basically start SQL
Server in single-user mode from the command prompt(sqlservr.exe -c -m).
Then open EM and restore master. Once master is restored, restart SQL
Server normally. In EM you will see that every database is suspect. That
means you will now have to individually restore all databases. Logins stay
in tact as well as databases and their users.
BOL. I usually just drop all users from the DBS and re-add them to the DBs
via the login. Any time you have a different master than was used at the
time database users were added to the database, there is a possibility for
orphaned users.

Yes, it can be reset. See BOL "Resetting the Suspect Status"

Normally, suspect indicates SQLs inability to complete recovery of a database. This can be for many reasons. The BOL topic above discusses one scenario. Other scenarios are based on having different master databases between 2 or more servers. Master DB is important here because all DB level info is stored in master. This how SQL Server knows what to recover.

When you create a DB, info about that DB is stored in master, such as file info and other id info. If you were to now apply a backup of the master with the new DB to another box, you now have a master that can not recover the new DB (Suspect status).

There is a DB id in master(sysdatabases). If I have the same user database, DB1, on both servers but on one master it has a DB ID of 1 and the other has a DB ID of 9 then this would also create the suspect status.

It all comes down to SQL Servers ability to recover databases based on info stored in master)