June 15, 2012 at 1:05 pm
I have a process where I need to do the following in SQL Server 2008:
1. Load data into MergedDataOrderedWorkTable
2. Drop table MergedDataOrdered
3. Rename table MergedDataOrderedWorkTable to MergedDataOrdered
I need a script that will kill any connections to the table in #2 prior to dropping it.
What is the best way in SQL to do this?
June 15, 2012 at 1:20 pm
Connections are not made to tables, they are made to the database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 15, 2012 at 1:29 pm
So if I am the owner of the table, can I drop it even if users are trying to query against it?
If I can't drop it in this case, how can I drop the connections to the database in a script?
June 15, 2012 at 1:42 pm
gary.morey (6/15/2012)
So if I am the owner of the table, can I drop it even if users are trying to query against it?If I can't drop it in this case, how can I drop the connections to the database in a script?
Yes, and no.
If a user is locking the table, i.e. performing updates/deletes, you will not be able to drop it until their transaction completes.
If it fails to drop or your transaction hangs you can find the user that is blocking you using sp_who2 and the kill command.
June 15, 2012 at 1:46 pm
I am the only user that does inserts / updates/ deletes to the table.
All of the other users can only do SELECTs against the table....so this means that I can drop the table, even if they are running SELECTs against it. Is this correct?
June 15, 2012 at 1:55 pm
gary.morey (6/15/2012)
I am the only user that does inserts / updates/ deletes to the table.All of the other users can only do SELECTs against the table....so this means that I can drop the table, even if they are running SELECTs against it. Is this correct?
Unless it is in an explicit, uncommitted transaction, yes.
June 15, 2012 at 2:25 pm
MysteryJimbo (6/15/2012)
gary.morey (6/15/2012)
I am the only user that does inserts / updates/ deletes to the table.All of the other users can only do SELECTs against the table....so this means that I can drop the table, even if they are running SELECTs against it. Is this correct?
Unless it is in an explicit, uncommitted transaction, yes.
In repeatable read or higher isolation level.
In default isolation (read committed) locks are dropped as soon as the read finishes1 even there is an explicit transaction
The drop will block until all the concurrent reads complete and then the table will go away. The table can't be dropped while users are selecting from it, but you can run the drop table, the lock necessary will be queued up and as soon as those users' selects are finished the table will be dropped.
(1) Massive simplification.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 2:30 pm
If there are many selects with dirty reads then they dont hold a lock but they do hold a latch (scaled down lock). This can cause the rare and very puzzling issue of something called a "live lock" (completely different to a deadlock). Your query may not be able to be actioned because all the other latches take higher priority over your request even though they have started after your query!
Dirty reads aka "with (nolock)" seems such a wonderful performance gain until you run into live locks at 4am on a production critical issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply