| Author |
Topic |
|
juicyapple
Posting Yak Master
176 Posts |
 Posted - 2008-03-02 : 23:17:46
|
| Hi, when there are lot of processes executing the select statement on a table, and I KILL all these processes (KILL spid) and drop the table, is the KILL statement have the first priority to execute and drop table can be done in short time? I am afraid that the select statement will take long time to be roll back if the table is huge...please advise. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-02 : 23:26:25
|
| select statement puts shared lock on the table by default, and you can't drop table if there is lock on it. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-02 : 23:27:10
|
A select statement shouldn't take long to kill, even if the table is big. if there are updates, inserts, deletes etc on a large table THOSE may take a long time.Why are you doing this?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-03-03 : 00:51:33
|
| I am doing database maintenance program which archive inactive data to another database, and move active data to a temporary table. The temporary table will replace the original table after I drop it. Before I drop the table and rename the temp table, I will kill all the user processes connecting to the table. These processes will only execute select statement.if one process executing select query and I use drop table to drop it without kill the process, will the drop transaction wait for the completeness of the select statement? will query time out or connection broken happens after long waiting time? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-03 : 19:38:07
|
So you select the records into a temp table, drop the main table and then recreate the main table? wrong way to go I think.Why not just insert the to-be-archived records into their destination using INSERT INTO statement, then do a delete using INNER JOIn to that table to remove the archived records from the main table.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-03-03 : 20:56:01
|
| because delete statement takes long running time, and my main constraint is the whole process must not interrupt user for long time. so drop and rename transaction takes few seconds (indexing few minutes) but deleting will take few hours and I need to stop user processes during the transaction. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-03 : 21:49:15
|
| You should do it as dataguru mentioned, but do it in small batches. It'll take longer to run, but it won't interfere with production. By killing out the users, you are impacting production. Smaller batches of deletes won't.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-03-03 : 22:53:14
|
| if change to small batches of deletes, then the frequent to kill user processes will be increased because they are executing select statement on these tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-03 : 22:59:38
|
| But you don't have to kill the users!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-04 : 03:17:14
|
Not even slap them on their heads?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2008-03-04 : 04:32:44
|
| I think I must miss out some points...how to delete the data if it is selected by processes?? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-04 : 23:29:13
|
| Have to wait unless uses nolock hint in select statement. |
|
|
|
|
priority of kill statement