January 8, 2018 at 11:50 am
Hi all
I am trying to restore a database ,but can not kill one connection.This is the query that is running on my db.
SELECT revisions.value AS Revision, scripts.value AS ScriptsLocation, migrations.value AS MigrationScriptsLocation
FROM [my db].sys.extended_properties AS revisions JOIN [my db].sys.extended_properties AS migrations ON migrations.minor_id = revisions.major_id JOIN [my db].sys.extended_properties AS scripts ON revisions.minor_id = scripts.minor_id WHERE revisions.name = 'SQLSourceControl Database Revision' AND migrations.name = 'SQLSourceControl Migration Scripts Location' AND scripts.name = 'SQLSourceControl Scripts Location';
Thank you
January 8, 2018 at 12:00 pm
Try
ALTER DATABASE YourDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
January 8, 2018 at 12:21 pm
Assuming that you have tried to terminate the session using 'kill spid', I run into this issue from time to time. You need to see what the session is doing after you issues the kill command. Sometimes it could be rolling back a transaction, which will take some time.
Even with D. Leidgen's ROLLBACK IMMEDIATE, you need to allow time for the rollback to complete. Alternatively, since you are restoring over it, you may consider dropping the database before the restore.
January 8, 2018 at 12:35 pm
I restarted my workstation and it's gone.I just wonder what does this query do?
January 8, 2018 at 12:38 pm
Assuming that you have tried to terminate the session using 'kill spid', I run into this issue from time to time. You need to see what the session is doing after you issues the kill command. Sometimes it could be rolling back a transaction, which will take some time.
Even with D. Leidgen's ROLLBACK IMMEDIATE, you need to allow time for the rollback to complete. Alternatively, since you are restoring over it, you may consider dropping the database before the restore.
i could not drop database because there was a active connection
January 8, 2018 at 1:42 pm
Do you have any third party applications running in SSMS on your machine?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2018 at 2:04 pm
Barcelona10 - Monday, January 8, 2018 12:35 PMI restarted my workstation and it's gone.I just wonder what does this query do?
With the reference to 'SQLSourceControl Database Revision' it kind of looks like you are using RedGate SQL Source Control.
Sue
January 8, 2018 at 2:05 pm
Looks like it has something to do with Source Control. Just a guess and I may be wrong.
January 8, 2018 at 3:57 pm
"Do you have any third party applications running in SSMS on your machine? "
I have RedGate SourceControl running on SSMS.But i've been backing up databases and restoring them all the time without having any issue
January 8, 2018 at 4:09 pm
Barcelona10 - Monday, January 8, 2018 3:57 PM"Do you have any third party applications running in SSMS on your machine? "
I have RedGate SourceControl running on SSMS.But i've been backing up databases and restoring them all the time without having any issue
Well, based on your post it was busy doing something at the time.
January 17, 2018 at 11:54 am
I usually take database offline before restore as some apps/services keep on connecting to database.
-- run against database which you want to take offline
declare @getdbid int = db_id(db_name())
declare @getspid int
declare @cmd nvarchar(4000)
declare c1 cursor for
select spid from sysprocesses where dbid=@getdbid and spid <> @@SPID
open c1
fetch next from c1 into @getspid
while @@FETCH_STATUS =0
begin
select @cmd= 'kill ' + CAST(@getspid as nvarchar(5))
exec (@cmd)
fetch next from c1 into @getspid
end
close c1
deallocate c1
set @cmd='use master; alter database ' + db_name(@getdbid) + ' set offline with rollback immediate '
exec( @cmd)
use master
go
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply