Table Lock Contention Issue

[dkillilea]

Three times a day I need to drop a table (say, TABLE1) and rename another freshly imported, prepped and indexed table (say, TABLE1_TEMP). They are the exact same table, one just has more current MTD info. I keep getting locked out from dropping TABLE1 as people are running selects and views off of it.

It may sound strange how I am dropping and re-naming tables, but it's the only way this particular set of tables will work. Plus it only takes a millisecond so users would only notice if they ran a view right at that millisecond.

I want to completely unlock TABLE1 so I can drop it when necessary. Is there a way I can build that into the table structure? Or am I stuck with the SQL Server lock contentions.

Keep in mind I do not need to insert update and delete rows (that's actually the beuty of it) so all users can be read only. I don't mind if they get cut off in the middle of a query either.

 

[mrdenny]

You would need to kill any users which are using the table. You can find this out by using the sp_lock command.

I would recommend using the sp_getapplock procedure to lock the table, then kill anyone who is using the table, then do the drop and rename.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com