none
Rename Database

    Question

  • I have a custom TFSBuild task that restores a backup of our database, runs some scripts against it, deletes the previous day's database, and the renames the new database to the same name as the one that was deleted. This has been working fine for weeks now, but started failing yesterday and also failed again today. The old database is deleted successfully, but the rename command fails.

    The code that runs is:

    Dim strRenameScript As String = String.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", _strTempDatabaseName) & Environment.NewLine & _
          "GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} MODIFY NAME={1}", _strTempDatabaseName, _strDatabaseName) & _
          Environment.NewLine &
    "GO" & Environment.NewLine & String.Format("ALTER DATABASE {0} SET MULTI_USER", _strDatabaseName) & _
          Environment.NewLine &
    "GO"

    Executed using the Microsoft.SqlServer.Management.Smo classes, which is interpreted as:

    ALTER DATABASE MyTempDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    ALTER DATABASE MyTempDatabase MODIFY NAME=MyLiveDatabase
    GO
    ALTER DATABASE MyLiveDatabase SET MULTI_USER
    GO

    This used to be fine, but now the batch fails after setting the database into single user mode, and the only error returned is:

    An exception occurred while executing a Transact-SQL statement or batch

    If I subsequently run the above script in SQLSMS it works fine. Does anyone know why this might have started failing all of a sudden?

    Wednesday, March 07, 2007 9:34 AM

All replies

  • Could you run the code below and post back the results?

    Chris

     

    SELECT *
    FROM master.sys.databases
    WHERE [name] IN ('MyTempDatabase', 'MyLiveDatabase')

    Wednesday, March 07, 2007 9:59 AM
  • from which database context u r executing this statement... be sure that u r not connected to this particular database, in that case the connection is already broken by this  statement

    Madhu

    Wednesday, March 07, 2007 10:47 AM
    Moderator
  • Madhu:
    I'm connecting to the master database both when deleting the old database and when renaming the new database:

    Dim cnTempDatabase As SqlConnection = New SqlConnection(String.Format(_strConnectionString, _strDatabaseServer, "Master"))
    Dim sqlServer As Server = New Server(New ServerConnection(cnTempDatabase))

    Chris:
    I've manually renamed the database now so daily development can continue, but I get one result back for the 'live' database (what the temp one was renamed to).

    I'll see if it fails again in the morning and if so I'll run the same query before doing anything and post back.

    Would the error have logged any more details in the SQL Server log?

    Wednesday, March 07, 2007 10:53 AM
  • OK, the statement failed again last night so I ran your query again. As expected, only the temp database (the one left in single user mode) is returned.

    Would a more verbose error have been logged anywhere?

    Friday, March 09, 2007 8:45 AM
  • if the error is not logged in SQL Server Event Log....most probably it means that the error is not occuring at the SQL Level but the Application level... capture the error from application or application log

     

    Madhu

    Friday, March 09, 2007 9:22 AM
    Moderator
  • There are lots of informational messages in the log about restoring the database, setting the old database to single user mode etc (although I note nothing about deleting it) and then the last entry is the about setting the temp database to single user mode (which happens just before the rename command):

    2007-03-09 06:16:42.68 spid55 Setting database option SINGLE_USER to ON for database HighwayP2Temp20070309.

    Then nothing. Yesterday (when the rename worked) there also wasn't any log entry about deleting the old database, so I guess that isn't an indicator.

    Any ideas?

    Friday, March 09, 2007 3:04 PM
  • Found more info:

    If I use the full backup of our database (~70Gb) then the rename fails, unless I have restarted the SQL Server instance at some point in the day beforehand. If I use the shrunk version of our database (~1.5Gb) then the rename is always fine. Perhaps this is something to do with SQL Server memory usage? Is anyone aware of any existing issues regarding memory utilisation that may affect a rename action?

    Wednesday, March 14, 2007 5:54 PM