Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
EXEC sp_refreshsqlmodule and you may also consider using synonyms - however, is this a very realistic use case? How often are you dropping a database, and renaming another one to take its place? –  Aaron Bertrand Aug 16 '13 at 13:32
    
There's no specific use case it's just something I observed dropping and recreating database during development –  sa555 Aug 17 '13 at 3:23
add comment

1 Answer

up vote 1 down vote accepted

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

so when you execute your store 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.