Sql-server – Renaming Sql Server database unusual result

sql serversql-server-2008-r2

This is some behaviour I observed from renaming databases. What is the cause of this? The example is simplified for the purposes of this question.

CREATE DATABASE [Database_A]
GO
CREATE DATABASE [Database_B]
GO
CREATE DATABASE [Database_B_Copy]
GO
-- create a table for testing purposes
USE [Database_B]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO 
INSERT INTO [Database_B].[dbo].[Table_1] (Column1) values ('[Database_B]')
GO
-- create an copy of the table with the same schema
USE [Database_B_Copy]
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO 
INSERT INTO [Database_B_Copy].[dbo].[Table_1] (Column1) values ('[Database_B_Copy]')
GO
-- create a stored procedure to demonstrate the problem
USE [Database_A]
GO
CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SELECT * FROM Database_B.dbo.Table_1
END
GO

[1]. when I run the stored procedure TestSP the result as expected is the contents of DatabaseB Table_1.

ID      Column1        
1       [Database_B]

[2]. Renaming Database_B to Database_B_Original will cause this error as would be expected:

Msg 208, Level 16, State 1, Procedure TestSP, Line 4
Invalid object name 'Database_B.dbo.Table_1'.

[3]. Taking Database_B_Original offline, the error from the stored procedure changes to:

Msg 942, Level 14, State 4, Procedure TestSP, Line 4
Database 'Database_B_Original' cannot be opened because it is offline.

[4]. I then renamed Database_B_Copy to Database_B. I would have expected the stored procedure to work since the a database named Database_B was available. However the error remains the same.

Finally if I restart Sql Server this magically fixes the problem and the output is, as expected:

ID      Column1        
1       [Database_B_Copy]

Is SQL Server caching the tables and database name information incorrectly? Is there a way to force Sql Server to refresh the information?

I would like to gain a better understanding of what the underlying cause is for this issue and whether it's by-design or an error with sql server.

Best Answer

What SQL Server is doing is, it is caching the query plan for 

 exec [Database_A].[dbo].[TestSP]

This can be found using :

SELECT 
    usecounts, cacheobjtype, objtype, text 
FROM 
    sys.dm_exec_cached_plans 
CROSS APPLY 
    sys.dm_exec_sql_text(plan_handle) 
WHERE 
    usecounts > 1 AND text LIKE '%TestSP%'
ORDER BY 
    usecounts DESC;
GO

When you execute your stored procedure during step 1, 2 or 3, it gets cached in the procedure cache.

Hence during your 4th step, it throws an error.

If you do a dbcc freeproccache, and then rerun the SP exec [Database_A].[dbo].[TestSP], it will work.

As @Aaron pointed out, it will work using EXEC sys.sp_refreshsqlmodule 'Database_A.dbo.TestSP' or recompiling the SP sp_recompile TestSP as well.

Ref: http://technet.microsoft.com/en-us/library/bb326754(v=sql.105).aspx

There is no need to restart SQL Server.

Note: Free ProcCache will flush out all the query plans, so its better to use sys.sp_refreshsqlmodule or sp_recompile.

Related Question