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?
Could you run the code below and post back the results?
Chris
SELECT *
FROM master.sys.databases
WHERE [name] IN ('MyTempDatabase', 'MyLiveDatabase')
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
|||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?
|||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?
|||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
|||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?
|||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?
