Kill all Connection only when they are locking a table

Question

Post reply

Kill all Connection only when they are locking a table

Rem70Rem

SSCertifiable

Points: 6166
More actions
August 16, 2010 at 7:48 am

#146681

Hi,
Every night I am transferring +/-50 tables from a staging database to a reporting one. Just before the transfer, I am killing all connections from reporting users to be sure that old tables can be dropped before moving the new ones. When I do this kill, sometimes I kill connections that don’t need to be kill and this cause some report to fail.
Is there a way to kill one the connections that have a lock on some specific tables?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Could you use TRUNCATE-INSERT or replication to move the data without dropping the tables ?

Or you could probably use the results of sp_lock to choose the spids to kill.

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 2

The way that I am copying the tables are:

1- Transfer al; table from stage to reporting using a temp table

2- Drop all reporting tables

3- Rename all temp tables to reporting table.

THanks

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

This works to Kill users of a set database, sure you could reconfigure to do table lever:

CREATE PROCEDURE usp_killDBConnections @DBName varchar(50), @withmsg bit=1
AS
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
IF @withmsg =1
PRINT CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB ' + @DBName
GO

Adam Zacks-------------------------------------------Be Nice, Or Leave

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 4

Hi Adam,

Thanks for your reply. Currently I am running something similar every night to kill my connection. I would like to *** the table level to this. Any hints?

What about the Truncate/Insert? Will this works even if someone is reading the table?

Thanks all.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

Another suggestion/question, the connections you don't want to kill, is there a different user list that is accessing these tables? Then you can build list of connections that are from other user and kill only those connections?

For the truncate/insert option this is how I have done it:

Two Databases, main db Report and another db Report_Stage on same server.

Delete all tables in Report_Stage database that are being ETL'd over.

Then truncate/insert each table from Report_Stage to Report database.

Because the tables are now on same server and loaded the processes is almost instant.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 6

Rem70Rem (8/16/2010)
Hi Adam,
Thanks for your reply. Currently I am running something similar every night to kill my connection. I would like to *** the table level to this. Any hints?
What about the Truncate/Insert? Will this works even if someone is reading the table?
Thanks all.

Sorry, couldnt work it out.

Anybody? How do you return all lock for a give database stating the table that is locked? Then we can feed that into the query above.

Cheers

Adam Zacks-------------------------------------------Be Nice, Or Leave

george sibbald SSC Guru Points: 104210 More actions · Answer 7

a possible way to do this is to get the ID of the table, and then see if the spid holds any locks on that object by querying on sys.dm_tran_locks matching ID to resource_associated_entity_id

something like (completely untested)

SELECT

request_session_id,

resource_type,

DB_NAME(resource_database_id),

resource_associated_entity_id,

FROM

sys.dm_tran_locks

WHERE request_session_id = yourspid and resource_associated_entity_id = (select OBJECT_ID(yourname))

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 8

@Rem70Rem

what happened here in the end? was any of the above useful?

---------------------------------------------------------------------

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 9

Hi George,

I was out of town for a few days. I will need to dig in this query. I am more used to work with Sybase. The resource table are new to me. If you get a change the explain a bit more your query, this will be really appreciated.

george sibbald SSC Guru Points: 104210 More actions · Answer 10

sys.dm_tran_locks is one of the new dynamic management views that came in with SQL2005 and allow you to extract information about what is going on within the SQL engine. It basically replaces sp_lock (which is still available) and provides much more useful info in a way that can be manipulated.

there will be a number of ways to do this but you should incorporate something similar to the code I provided in the logic of your kill process. You will have a list of active spids and you know the objects you want to check for locks on. You can query the view with those criteria and find out if the spid holds locks on those resources.

So you could use if exists, select count(*) or check @@rowcount after the query. If the query returns no rows it is not holding locks on the objects you are interested in, if it does return rows, it is.

hope that helps

---------------------------------------------------------------------