October 26, 2003 at 12:27 pm
I'm downloading a full backup nightly done at another location and using sqldmo to force a restore of the local database with the backup. Works well unless a user leaves Query Analyzer open and connected to the database, then the restore fails due to lack of exclusive access. Is there a way to forceable disconnect all users and allow my restore to complete?
October 26, 2003 at 10:06 pm
Never mind, changed my search and found some scripts that showed how using sp_who and kill. Worked great.
October 26, 2003 at 11:00 pm
Howdy I had a similar problem when doing log shipping to my standby servers.
I have just finsihed writing this script and thought you might be interested.
Remember please test before running on any production databases!!!
CREATE procedure USP_DBMAINT_killspid @dbname varchar(100)
--Creates a list of all spids currently connected to a database and then termintates all spids connected.
-- created griff 27 Oct 2003
as
declare @thespid as int
declare @name as varchar(20)
declare @myquery as varchar (200)
declare @query varchar(1000)
declare @thedbid as varchar(100)
SELECT @thedbid = [dbid] from master..sysdatabases where [name] = @dbname
set @myquery = 'select req_spid from master..syslockinfo where rsc_dbid = ' + @thedbid
Create table #spidnames (id int identity(1,1) ,name varchar(200))
print @myquery
insert #spidnames(name)
exec (@myquery)
declare mycursor cursor for
select name from #spidnames
open mycursor
fetch next from mycursor into @name
while (@@fetch_status =0)
begin
set @query = 'kill '+ @name
print @query
exec (@query)
fetch next from mycursor into @name
end
drop table #spidnames
close mycursor
deallocate mycursor
GO
Edited by - growl on 10/26/2003 11:07:07 PM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply