Killing Locks by Object - SS2005

splocI can't restore a database due to a locking issue. While I've killed
the Process in Activity Monitor, I still see the database listed on the Locks
By Object page. The Process ID is a negative number. Any ideas as to what I
can do to get rid of this lock?
Here's the output from sp_lock: -2 7 0 0 DB
S GRANT
Thanks in advance.
JohnOn Oct 5, 1:49 am, John Roberts
<JohnRobe...@.discussions.microsoft.com> wrote:
> splocI can't restore a database due to a locking issue. While I've killed
> the Process in Activity Monitor, I still see the database listed on the Locks
> By Object page. The Process ID is a negative number. Any ideas as to what I
> can do to get rid of this lock?
> Here's the output from sp_lock: -2 7 0 0 DB
> S GRANT
> Thanks in advance.
> John
After killing the process you may try putting database in single user
mode which would prevent application or user establishing connection.
Thanks
VS|||Thanks for the response..
When I do a select distinct req_transactionuow, req_transactionID from
syslockinfo where req_spid = -2
I see the follwing:
req_transactionuow req_transactionID
--
--
00000000-0000-0000-0000-000000000000 0
When I try to kill this UOW using the guid of all zeroes, we get the
following error:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000}
does not exist.
Anybody out there familiar with killing orphaned transactions where the UOW
GUID is all zeros? My only solution now is to restart the service and, as
you might have imagined, that's NOT the only database running!!
Thanks in advance.
John
"vijay" wrote:
> On Oct 5, 1:49 am, John Roberts
> <JohnRobe...@.discussions.microsoft.com> wrote:
> > splocI can't restore a database due to a locking issue. While I've killed
> > the Process in Activity Monitor, I still see the database listed on the Locks
> > By Object page. The Process ID is a negative number. Any ideas as to what I
> > can do to get rid of this lock?
> >
> > Here's the output from sp_lock: -2 7 0 0 DB
> > S GRANT
> >
> > Thanks in advance.
> >
> > John
> After killing the process you may try putting database in single user
> mode which would prevent application or user establishing connection.
> Thanks
> VS
>

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database

> before daily restore job in DR machine.

>

> For testing, the script is getting the spid from sysprocesses and

> sysdatabases and store the specific spid which accessing the user DB

> into cursor and sp_who spid.

You're making this way harder than it has to be:

USE master

GO

ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Keeping SQL 2000 databases in synch after restore.

We have 4 different databases, running on sql server 2000. I scheduled
the database to be backed up at 5:00, and every hour for the trans
log.
If we have a hardware failure and have to restore from backup. how do
I make sure that the database are all starting at the same time. (if
database common takes 2 min to back up and database customer takes 30
min to back up) how do I make sure these 2 database are at the same
point in time.
Is there anything I can do in my sql backup job to have the backup's
end at the same time?
Currently all of the backup are different jobs.
Nicholas.GadaczSo you're saying there are transactional integrity reasons why the DB's all
need to be restored to the exact same point in time?
RESTORE LOG allows you to specify STOPAT for point in time recovery, but
RESTORE DATABASE doesn't.
I can't think of a clean way to do that for a full database without using
tran log restores...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz|||I really don't mean to be a smart ass here. I realize it may not be the
answer you are looking for. But this is kind of a design issue. If there
are transactional integrity issues, the easiest and cleanest way is to put
those objects in the same database. You may have name conflicts, but those
will be much easier to resolve now than the issues you have started to run
into now. What if tomorrow you want to send a snapshot replication of five
related tables? You will have the same issue.
If you do take my advice and put your databases into one, a good way to
avoid naming conflicts in your code, is to script one of the databases in
its entirety, do a search and replace and change all occurences of
conflicting names at the object definition as well as the references in code
simultaneously. Then you can put the two together without conflict. Again,
if you decide to do that, I can be made available for assistance remotely.
Pls remove NO_SPAM(s) from my email if you need to reach me.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz