Início / dba / Perguntas / 48237
Accepted
sa555
Asked: 2013-08-17 04:47:25 +0800 CST

Renomeando o resultado incomum do banco de dados SQL Server

  • 772

Este é um comportamento que observei ao renomear bancos de dados. Qual é a causa disso? O exemplo é simplificado para os propósitos desta questão.

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]. quando executo o procedimento armazenado TestSP, o resultado esperado é o conteúdo de DatabaseB Table_1.

ID      Column1        
1       [Database_B]

[2]. Renomear Database_B para Database_B_Original causará este erro como seria de esperar:

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

[3]. Colocando Database_B_Original offline, o erro do procedimento armazenado muda para:

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

[4]. Em seguida, renomeei Database_B_Copy para Database_B. Eu esperava que o procedimento armazenado funcionasse, pois um banco de dados chamado Database_B estava disponível. Porém o erro continua o mesmo.

Finalmente, se eu reiniciar o Sql Server, isso corrige magicamente o problema e a saída é, como esperado:

ID      Column1        
1       [Database_B_Copy]

O SQL Server está armazenando em cache as tabelas e as informações de nome do banco de dados incorretamente? Existe uma maneira de forçar o Sql Server a atualizar as informações?

Gostaria de entender melhor qual é a causa subjacente desse problema e se é intencional ou um erro com o servidor SQL.

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    O que o SQL Server está fazendo é armazenar em cache o plano de consulta para

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

    Isso pode ser encontrado usando:

    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

    Quando você executa seu procedimento armazenado durante a etapa 1, 2 ou 3, ele é armazenado em cache no cache do procedimento.

    Portanto, durante a 4ª etapa, ele gera um erro.

    Se você fizer um dbcc freeproccache, e depois executar novamente o SP exec [Database_A].[dbo].[TestSP], ele funcionará.

    Como @Aaron apontou, ele também funcionará usando EXEC sys.sp_refreshsqlmodule 'Database_A.dbo.TestSP'ou recompilando o SP sp_recompile TestSP .

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

    Não há necessidade de reiniciar o SQL Server.

    Observação: o Free ProcCache liberará todos os planos de consulta, portanto, é melhor usar sys.sp_refreshsqlmoduleou sp_recompile.

    • 4

relate perguntas