Tuesday, 5 November 2013

When you absolutely positively got to kill every mother ****er in the room ACCEPT NO SUBSTITUTES!!!!

Ever been in the situation where you need to restore a database, delete, take off line or detach, but "exclusive access cannot be obtained as the database is in use"?

Then here is the script for you.  Use with caution, it will end any open transactions, and is not recommended for any thing but a database that's now junk, ie to be deleted or restored over.

declare @SQLText varchar(8000) ,@spid int
select @spid = min(spid) from master..sysprocesses where dbid = db_id('<Databasename>')
while @spid is not null
 select @SQLText = 'kill '+convert(varchar(5),@spid)
 select @SQLText
 execute (@SQLText)
 -- inc counter
 select @spid = min(spid) from master..sysprocesses where dbid = db_id('<databasename>') and spid > @spid

Remember when restoring a database over the top, its always best to not drop said database first. As this will cause the restore to have to recreate file's ect. And will make it take longer.


No comments:

Post a Comment

Your views:-