How to identify locks in SQL server and kill them

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to identify locks in SQL server and kill them

Author

Topic

newdev
Starting Member

5 Posts

Posted - 2007-03-09 : 10:17:23

I have to find locks which have been caused by some particular users before the scheduled jobs kick off and kill the locked processes so that the scheduled job executes without failing. How to do this in SQL server? please help

Thanks in Advance

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 10:25:37

See sp_lock and kill in Books Online.

newdev
Starting Member

5 Posts

Posted - 2007-03-09 : 10:28:04

Thanks for the Reply:
I want to do it in a stored procedure< iI have no idea how to do this.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-09 : 10:37:10

There's a good guide here:

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c03ppcsq.mspx#EICAE

Why do you need it in a stored procedure?

Drew

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-09 : 10:39:46

i think there's a procedure here called spMurder ha ha ha
make a search on the 'old' forums

--------------------
keeping it simple...

newdev
Starting Member

5 Posts

Posted - 2007-03-09 : 10:41:13

THNX Drew,
I need to automate this process, which kills unwanted processes.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-09 : 10:50:08

How do you know which processes are unwanted?

Drew

newdev
Starting Member

5 Posts

Posted - 2007-03-09 : 12:15:13

i get it from the sysprocesses table. i get spid, username from the sysprocesses table which is a table under master database.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 12:35:51

If you've already got the spids, then just use KILL to shut them down.

newdev
Starting Member

5 Posts

Posted - 2007-03-09 : 13:05:58

this has to be automated -- scheduled job.
The stored procedure should get the spids and user names and kill the processes

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-10 : 19:30:49

maybe you should find out why the locks happen and get rid of them.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources