[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1174
  • Last Modified:

SQl server 2000 stored proc to detach database gives Error "Only user processes can be killed. Cannot detach database as it is in use"

SQl server 2000 stored procedure to detach database gives Error "Only user processes can be killed. Cannot detach database as it is in use"

The sp locates all the user processes spids for that database and executes kill spid.
Then sp_detach db procedure is executed.
Kill spid sometimes seems to give the error and then detach fails.

Any suggestions to get this working.
 

0
ashugarg00
Asked:
ashugarg00
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you, by any chances, connected yourself to the database you are trying to detach?
0
 
ashugarg00Author Commented:
no i am not . Also, this error pops up intermittently. On most occasions it works fine.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how often do you run that detach? regulary?
can you give a bit more insight in what/how you are doing?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
ashugarg00Author Commented:
This detach is sometimes run very frequently , once every 15 mins.  

The application needs an mdf file to be sent across to a web service.
My application creates a copy of an existing mdf file and attaches it to a new database, adds some data using some stored procedure into the attached database. Then the database is detached and the mdf with the data inside is sent across.   This detach is failing.  The sp code snippet is as follows:

 SELECT spid FROM MASTER..SYSPROCESSES  
  WHERE dbid=db_id('db_name')

loop through all those spids and then execute  
 SET @sql_KillProcess = 'kill ' + cast(@nSPID AS varchar(200))    
   EXEC (@sql_KillProcess)  

then finally outside the loop call EXEC sp_detach_db @vcDBName  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The application needs an mdf file to be sent across to a web service.
I would not make it that way. I would issue a BACKUP DATABASE statement, and send the backup file, and restore the backup file instead.
0
 
ashugarg00Author Commented:
I am not quite clear on what you mean. Do you mean that instead of detaching the database, take its backup and send that. But what should be done the next time? There will be multiple databases then that would be generated everytime.

I am not in a state to change the design right now. But I would be glad if there is a way to fix the bug by changing the stored procedure ?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Do you mean that instead of detaching the database, take its backup and send that.
yes, exactly.

>But what should be done the next time?
I guess you wonder about the "attach" process. well, instead of attaching, do a restore WITH REPLACE, which will replace the database that is attached.
using backup/restore will be more secure than doing attach/detach...

note: the design will not change,
* instead of issuing sp_detach_db, you will issue a BACKUP (WITH INIT) statement instead.
* instead of sending the .mdf file, you send the backup file
* instead of attaching the .mdf file, you do a RESTORE from the backup file.

should be possible very easily with your design

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now