This is a bug I found within SQL 2008 (and R2) which has caused me endless problems within my system. I now have a workaround, but it’s clumsy and I really don’t appreciate having to do it.
Background
I was at a company in which they had a very large reporting database holding masses of aggregate data. The data in this database changed with such frequency that it was far more time, cpu, and io efficient to drop and re-create all tables within the master copy of this database than it was to run insert / update / delete (or merge) statements.
Therefore what happened was this… a staging reporting database (we’ll call it “RepDataStaging”) was generated each night from scratch involving the dropping and re-creating of all tables. Then, because a backup / restore was too time consuming and this was connected to a 24 hour reporting system, we used the minimal downtime option of renaming the database to “flip” them. This only takes a couple of seconds.
So we renamed as follows:
• RepDataLive -> RepDataOld
• RepDataStaging -> RepDataLive
• RepDataOld -> RepDataStaging
This worked perfectly within SQL Server 2005 and all versions before. However, in SQL Server 2008 (and R2) we started getting reports of incorrect data and errors appearing.
After a lot of head scratching and investigation I found that SQL Server was not honouring a name swap with regard to views in other databases. I will explain this more clearly in the following example:
Example
Create 3 databases (I’m going to use the most basic of statements and allow the SQL defaults to be used for all database creation settings)…
use master
go
create database repDataLive
go
create database repDataStaging
go
create database testFlip
go
Now, let’s create a basic table on each repData database…
create table repDataLive.dbo.repDataTable
(
randomData varchar(100)
)
go
create table repDataStaging.dbo.repDataTable
(
randomData varchar(100)
)
go
Let’s put some very simple data into the table so that we can differentiate between the two quite clearly…
insert into repDataLive.dbo.repDataTable
select 'This is the original Live database'
insert into repDataStaging.dbo.repDataTable
select 'This is the original Staging database'
go
Create a view
use testFlip
go
create view dbo.myTestView
as
select *
from repDataLive.dbo.repDataTable
go
We can test this simply enough by just running a select on the view…
select *
from testFlip.dbo.myTestView
Also, for clarity, we’ll pull up the database IDs for the databases so that we can verify our name changes once we make them…
select database_id, name
from sys.databases
where name like 'rep%'
It’s now time to make the name changes in order to “flip” the databases…
use master
go
exec sp_renamedb 'repDataLive', 'repDataOld'
go
exec sp_renamedb 'repDataStaging', 'repDataLive'
go
exec sp_renamedb 'repDataOld', 'repDataStaging'
go
Double check this in the databases system table…
select database_id, name
from sys.databases
where name like 'rep%'
And we have indeed swapped the two databases which is exactly what we require.
However, this is where everything falls down… If you select from the view in testFlip we get the following:
select *
from testFlip.dbo.myTestView
Yes… there’s no change at all. Although the databases have changed, SQL Server has not fully registered this and now all views are pointing to the original, not the new database. As such, in the case of the firm I was working for, all the data was now out of date and so producing unexpected results to queries.
Solution
The way to workaround this is to “refresh the view”. This is demonstrated as follows:
As this screenshot shows, running sp_refreshView fixes the problem.
That’s great, but not entirely helpful when you have a server with in excess of 100 referencing views!!! As such I had to create a loop which runs as the next step in the scheduled task of the job performing the flip.
This was not good enough in itself though as there is inevitably the odd view that will not recompile maybe because it is old, columns no longer exist, and it has never been dropped.
These will cause sp_refreshView to fail with a critical error that cannot be caught in a try and catch statement. Because of this I ended up creating a 2 step loop on the end of my flip job which looked like this…
Step 5 was a guaranteed successful step as it was simply “select 1” and therefore it always fired back into step 4 which was running my sp_refreshView code.
In order to stop an endless loop, if a view failed to recompile, the name is entered into a table and skipped on the next run. This meant that, once successful, all my views were now looking at the correct data and I also had a table containing any views that needed investigating and probably deletion.
The full script I used is as follows: (this creates all tracking tables if they do not exist but you will have to change database names where applicable)
set nocount on
use boredDBA
go
if OBJECT_ID('dbo.viewRefreshException') is null
begin
create table dbo.viewRefreshException
(
dbName varchar(100) not null,
objectName varchar(250) not null,
objectType char(1) not null
)
end
use master
go
if OBJECT_ID('tempDB..#views') is not null drop table #views
go
declare @flippedDB varchar(100) = 'repDataLive' -- change accordingly
declare @db table
(
id tinyint identity(1, 1),
dbName varchar(100)
)
insert into @db
select name
from sys.databases
create table #views
(
id int identity(1, 1),
dbName varchar(100),
name varchar(250)
)
declare @counter int = 1, @dbName varchar(100), @viewName varchar(250),
@sql nvarchar(max)
declare @reset table
(
id int identity(1, 1),
cmd nvarchar(max)
)
insert into @reset
select 'if exists (select * from ' + dbName + '.sys.objects where name = ''' +
objectName + ''' and modify_date > dateadd(dd, -1, current_timestamp))
begin
delete from boredDBA.dbo.viewRefreshException
where dbName = ''' + dbName + '''
and objectName = ''' + objectName + '''
end'
from boredDBA.dbo.viewRefreshException
while @counter <= (select MAX(id) from @reset)
begin
select @sql = cmd
from @reset
where id = @counter
exec sp_executeSQL @sql
set @counter += 1
end
set @counter = 1
while @counter <= (select MAX(id) from @db)
begin
select @sql = 'insert into #views
select table_catalog, table_name
from ' + dbName + '.information_schema.views
where view_definition like ''%' + @flippedDB + '.%''
and table_catalog + ''..'' + table_name not in
(
select dbName + ''..'' + objectName
from boredDBA.dbo.viewRefreshException
where objectType = ''V''
)'
from @db
where id = @counter
exec sp_executeSQL @sql
set @counter += 1
end
set @counter = 1
while @counter <= (select MAX(id) from #views)
begin
select @dbName = dbName, @viewName = name,
@sql = 'use ' + dbName + '; exec sp_refreshView ''' + name + ''''
from #views
where id = @counter
insert into boredDBA.dbo.viewRefreshException
select @dbName, @viewName, 'V'
exec sp_executeSQL @sql
delete from boredDBA.dbo.viewRefreshException
where objectName = @viewName
and dbName = @dbName
set @counter += 1
end
set nocount off