Error message 913

I've been having intermittent issues with SQL Server 2000 server on Windows 2000 Server. This server acts as a development server and is on the same network as our production server which runs Windows 2003 Server. Spare me the lecture about running different OS's on production and development servers, I wasn't the individual who setup the environment, just the one who got saddled with the responsibilitiy of making it useful for our staff. Anyway, the issue I've been dealing with is intermittent 913 error messages similar to what follows:

Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 80. Database may not be activated yet or may be in transition.

I have several tools I've developed which I use to backup the production databases then restore the backups onto the development server. We use these database copies for reproducing/researching issues that our customers are having. One of the tools used to copy the databases is an ASP.NET application and the other tool is a VB 6 application. In the past the only tool I've had a problem with is the ASP.NET application, intermittently it would attempt to transfer the database and fail with the 913 error. The VB 6 application hasn't failed yet but is rarely used. Both applications simply execute a stored procedure which does the actual work. I've never seen the problem surface while manually running the stored procedure from query analyzer until today. The guts of the stored procedure is below, the procedure is aptly named CopyDatabaseToDevelopment.

As I stated, today I encountered the problem using query analyzer. I've checked the obvious, the database was restored successfully and is accessible on the development server. I also found a related issue here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=664023&SiteID=1. I checked the collation of both servers and they are identical, however, it led me to try using the procedure and changing the case of the database name I supplied which caused the error to disappear. Is there any possibility that there's still an issue with SQL Server causing these 913 errors that didn't get resolved? Is there any chance the difference in OS could have an impact on this?

Following are the specs of the production server:
SQL Server Enterprise Edition (2 node cluster)
OS - Microsoft Windows NT - 5.2 (3790)
Product Version - 8.00.2039 (SP4)
Server Collation - SQL_Latin1_General_CP1_CI_AS
SELECT @.@.Version = "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"

Following are the specs of the development server:
SQL Server Enterprise Edition (non-clustered)
OS - Microsoft Windows NT - 5.0 (2195)
Product Version - 8.00.2039 (SP4)
Server Collation - SQL_Latin1_General_CP1_CI_AS
SELECT @.@.Version = "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)"

As you might be able to tell, I am using linked servers and I'm guessing this might be part of the problem, however, I was able to reproduce the issue on the development server locally by using the same SQL statement with the exact same case. Changing the case of only a single letter in the database name solved the problem. Note, the statement causing the error is the last statement in the procedure, this statement inserts a single row into a table in the database on the development server.

CREATE PROCEDURE CopyDatabaseToDevelopment
@.DatabaseName NVARCHAR(256)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.DynamicSQL NVARCHAR(4000),
@.CRLF NVARCHAR(2),
@.LogicalFilename NVARCHAR(256),
@.PhysicalFilename NVARCHAR(256),
@.SlashPointer INTEGER,
@.LastSlashPointer INTEGER,
@.ShareExists BIT,
@.RestoreSQL NVARCHAR(4000),
@.SharedFolder SYSNAME,
@.BackupFilename NVARCHAR(256),
@.CheckDatabase INTEGER
SELECT @.SharedFolder = dbo.BackupFolder(),
@.ShareExists = dbo.FolderExists(dbo.BackupFolder()),
@.CRLF = CHAR(13) + CHAR(10)

IF @.ShareExists = 1
BEGIN
SET @.BackupFilename = dbo.BackupFilename(@.DatabaseName, GETDATE(), 'full')

EXECUTE BackupDatabase
@.DatabaseName = @.DatabaseName,
@.Init = 1,
@.BackupDescription = 'Backup for copying to the development server.',
@.Differential = 0,
@.Verify = 0

CREATE TABLE #FilesTable
(LogicalFilename VARCHAR(256),
PhysicalFilename VARCHAR(256))

SET @.DynamicSQL = 'INSERT #FilesTable' + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + 'SELECT [NAME],' + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + ' [Filename]' + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + 'FROM ' + @.DatabaseName + '.dbo.sysfiles' + @.CRLF

EXECUTE master.dbo.sp_executesql
@.DynamicSQL

SET @.DynamicSQL = 'RESTORE DATABASE ' + @.DatabaseName + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + 'FROM DISK = @.BackupFilename' + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + 'WITH REPLACE,' + @.CRLF
SET @.DynamicSQL = @.DynamicSQL + 'PASSWORD = ''' + dbo.DatabaseBackupPWD() + ''',' + @.CRLF

DECLARE Files_Cursor CURSOR FOR
SELECT LogicalFilename,
PhysicalFilename
FROM #FilesTable

OPEN Files_Cursor

FETCH NEXT
FROM Files_Cursor
INTO @.LogicalFilename,
@.PhysicalFilename

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.PhysicalFilename = RTRIM(@.PhysicalFilename)
SET @.SlashPointer = CHARINDEX('\', @.PhysicalFilename)

WHILE @.SlashPointer <> 0
BEGIN
SET @.LastSlashPointer = @.SlashPointer
SET @.SlashPointer = CHARINDEX('\', @.PhysicalFilename, @.SlashPointer + 1)
END

SET @.PhysicalFilename = RIGHT(@.PhysicalFilename, LEN(@.PhysicalFilename) - @.LastSlashPointer)

SET @.DynamicSQL = @.DynamicSQL + ' MOVE ''' + RTRIM(@.LogicalFilename) + ''' TO ''D:\Program Files\Microsoft SQL Server\MSSQL\data\' + @.PhysicalFilename + ''',' + @.CRLF

FETCH NEXT
FROM Files_Cursor
INTO @.LogicalFilename,
@.PhysicalFilename
END

SET @.RestoreSQL = LEFT(@.DynamicSQL, LEN(@.DynamicSQL) - 3)

CLOSE Files_Cursor
DEALLOCATE Files_Cursor

DROP TABLE #FilesTable

-- Kill all active processes using the database
SET @.DynamicSQL = 'EXECUTE DBAWork.dbo.KillDatabaseProcesses ''' + @.DatabaseName + ''''

EXECUTE [DEVSQL].[master].[dbo].sp_executesql
@.DynamicSQL

-- Restore the database
EXECUTE [DEVSQL].[master].[dbo].sp_executesql
@.RestoreSQL,
N'@.BackupFilename NVARCHAR(256)',
@.BackupFilename

-- Delete the backup file
SET @.DynamicSQL = 'DEL ' + @.BackupFilename

EXECUTE master.dbo.xp_cmdshell
@.DynamicSQL,
NO_OUTPUT

SET @.DynamicSQL = 'INSERT [' + @.DatabaseName + '].dbo.UsersMaster VALUES (''DEVUSER'', 4)'

-- Add the development user
EXECUTE [DEVSQL].[master].[dbo].sp_executesql
@.DynamicSQL
END
END

You should never run different OS versions between Dev and production..... :) Sorry, couldn't resist. You have an environment that looks like a large number of other environments around the world. Keeping everything matched up is a perfect world and very few environments are in a perfect world.

For the first question, NO, the different OS versions aren't going to cause something like this to happen. The server collation is case insensitive and I'm assuming the database, table, and column collations are all also case insensitive. If that is the case, changing the case of a letter should have absolutely no effect at all. I would suggest opening this as a support case, because you shouldn't be seeing that error randomly popping up and I don't see anything wrong with the code.

|||

There is a known issue on SQL 2000 with getting the 913 error in a case like this if the RESTORE resulted in the database ID changing. The suggested workaround is to use DBCC FREEPROCCACHE after the RESTORE to eliminate any cached plans that might have the old database ID in them.

If this does not work for you then you should work with product support to see if a fix or a different workaround can be provided such as possibly adding a WITH RECOMPILE to the EXECUTE.

|||

After seeing Peter's response I realize the issue isn't quite as random as I thought and what he said makes a great deal of sense. As stated earlier I use two applications to copy the databases from the production server to the development server, a VB 6 application and ASP.NET application. The VB 6 application is what actually creates the production databases so it is only used to copy the database to the development server once over the life of the database. Since the database never previously existed on the development server when the VB 6 application is used to copy the database there's no way an execution plan could be cached so that would explain why I've never seen the VB 6 application fail to copy a database. The other thing is I've seen the ASP.NET application successfully copy a database from server to server one day then the very next day it would fail which also would be explained by an execution plan being cached on the development server. The fact that the database ID's reported in the error messages don't exist in the master..sysdatabases table also lends credence to Peter's explaination.

I've added code to my procedure to clear the procedure cache as suggested and will see how it works.

My only concern is that I had to come to this forum to discover more about the issue. Was I simply not doing a good job of searching msdn.microsoft.com or is the problem that there's not enough information about this issue published? I wonder because I've been dealing with this issue for months, have done much research to try to resolve the issue and am disappointed that there seems to be little to no information about it even though it's a "known issue".