September 28, 2009 at 6:18 pm
I am performing a full backup restore operation of 2 production databases from one server to another overnight. Part of the process is the following script that kills all the processes on the target server for the database that is about to be restored:
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'ISO'
DECLARE @sql varchar(max)
SET @sql = ''
SELECT @sql = @sql + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
EXEC(@SQL)
The process has been working fine for months but in the past several weeks I have been getting a failure once or twice a week on the database that is in the script above. Nothing has changed in terms of the scripts that are running and the job executes at midnight (which at my company means no one is accessing either the production or the DB on the target server). The log on the failure is listed below:
Date9/28/2009 12:02:02 AM
LogJob History (Refresh ISO Data)
Step ID1
ServerALPGASDW01
Job NameRefresh ISO Data
Step NameRefresh ISO DB
Duration00:00:30
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NASA\ALPGA_ISOSQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 12:02:02 AM Error: 2009-09-28 00:02:31.98 Code: 0xC002F210 Source: Kill all processes on the target db server for this DB so it can be restored Execute SQL Task Description: Executing the query "DECLARE @DatabaseName nvarchar(50) SET @DatabaseNa..." failed with the following error: "Only user processes can be killed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:02:02 AM Finished: 12:02:31 AM Elapsed: 29.547 seconds. The package execution failed. The step failed.
Is there a SPID or SPIDS that I should maybe be filtering out of my script to allow the process to running relaibly?
Thanks in advance.
-rfc
September 28, 2009 at 9:02 pm
The way I read it it seems there is a system process running against the database. Do you have any backup software that is taking a backup of the server that may not have been previously?
You might add in some code prior to running that section that takes a picture of what is going on just for troubleshooting purposes. Maybe the current connections and processes SQL is running.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 30, 2009 at 6:22 am
Instead of killing the processes, have you thought about doing this prior to the restore:
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname SET ONLINE
That ought to kill everything, and give you a quiet database for the restore.
September 30, 2009 at 7:52 am
As said before its a system process connected to the database, could be a checkpoint process, a cleanup task, number of things. Either add a where spid > 50 or better as suggested offline the database just before the restore.
the restore itself will bring the database back online.
---------------------------------------------------------------------
September 30, 2009 at 8:23 am
Got it working fine with a variation on the setting database OFFLINE suggestion by jdenicola. I am setting the database into SINGLE USER mode prior to the install and then back to MULTI USER following the install. Seems to be working fine now. Thanks to all for the suggestions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply