德瑞克：SQL Server 學習筆記: [SQL Server] FIX: Invalid object name 'master.dbo.spt

2018-01-28

[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