[SQL Server] FIX: Invalid object name 'master.dbo.spt_values'

How to fix: Invalid object name 'master.dbo.spt_values' ?

• It's only need to execute the u_tables.sql file with Dedicated Administrator Connection(DAC).
• No downtime.

For example: 

• Getting error on ALT+F1 in SSMS

---

Symptoms

• Getting error on Database Properties in SSMS
• Getting error in sp_help 'objectname'
• Getting error on ALT+F1 in SSMS

Encounters errors and the following messages:

Msg 208, Level 16, State 1, Procedure sp_help, Line 103 [Batch Start Line 5]
Invalid object name 'master.dbo.spt_values'.

Error 1: Getting error on Database Properties in SSMS

-- figure 11_Getting error on Database Properties in SSMS




-- figure 12_Getting error on Database Properties in SSMS




Error 2: Getting error in sp_help 'objectname'

-- figure 21_Getting error in sp_help 'objname'




Error 3: Getting error on ALT+F1 in SSMS

ALT+F1 Keyboard Shortcuts

-- figure 31_ALT+F1

---

Cause

• From the error it looks like view object: dbo.sp_values is not found in the Resource database(mssqlsystemresource.mdf).
• Don't restore the master database.

---

Resolution

• It's only necessary to use a Dedicated Administrator Connection(DAC), and execute the u_tables.sql file in Query Editor.
• No downtime.

---

FIX: Invalid object name 'master.dbo.spt_values'


Demo Version: SQL Server 2017


To connect to a server using the DAC


01. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

02. In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance.

• For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

03. Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

• The connection is made.
• If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

-- figure 61_DAC




-- figure 62_DAC




Execute the u_tables.sql file in Query Editor

The u_tables.sql file is located in the following folder:

• C: represents the drive where you installed the SQL Server program files
• Version number 14 is SQL Server 2017.

C:\Program Files\Microsoft SQL Server\MSSQL14.\MSSQL\Install

-- figure 41_u_tables.sql file is located in the following folder

Execute the u_tables.sql file in Query Editor, and Fix this issue.

• It has been successfully completed.

-- figure 63_Fix this issue

Starting u_Tables.SQL at  26 Jan 2018 07:51:34:150
This file creates all the system tables in master.
Creating view 'spt_values'.
drop table spt_monitor ....
Creating 'spt_monitor'.
Grant Select on spt_monitor
Insert into spt_monitor ....
Finishing at  26 Jan 2018 07:51:34:277

Verification 

Execute the sp_help.

-- figure 71_sp_help

 

---

Execute the u_tables.sql file without use DAC


Execute the u_tables.sql file without use DAC,  then the query encounters errors and the following messages:

-- figure 51_Error_Execute the u_tables.sql file without use DAC

Starting u_Tables.SQL at  26 Jan 2018 07:49:09:660
This file creates all the system tables in master.
Creating view 'spt_values'.
Msg 208, Level 16, State 1, Procedure spt_values, Line 7 [Batch Start Line 49]
Invalid object name 'sys.spt_values'.
sp_MS_marksystemobject: Invalid object name 'spt_values'
Msg 15151, Level 16, State 1, Line 62
Cannot find the object 'spt_values', because it does not exist or you do not have permission.
drop table spt_monitor ....
Creating 'spt_monitor'.
Grant Select on spt_monitor
Insert into spt_monitor ....
Finishing at  26 Jan 2018 07:49:09:950

---

Sample Code

20180128_spt_values_issue

https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

References

Resource Database
https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

Use the system_health Session
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

Using a Dedicated Administrator Connection
https://technet.microsoft.com/en-us/library/ms189595(v=sql.105).aspx

Diagnostic Connection for Database Administrators
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators

SQL Server Management Studio Keyboard Shortcuts
https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts