Error message 913

• Wednesday, February 07, 2007 10:00 PM

   

   
  

  0

  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

   

  • Reply
  • Quote