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.
| Author |
Topic |
|
Jampandu
Starting Member
12 Posts |
 Posted - 2014-03-18 : 00:31:11
|
| Hi Experts,I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.In details,I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause),if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.Is there any SQL Server hints to avoid blocking ..Please suggest if there is any other alternative way. Thanks! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-18 : 05:04:07
|
| What is your batch size? May be you need to reduce it further. How are you separating batches? Using a looping construct (WHILE etc.) or GO# ? Perhaps you also need to insert a WAIT FOR delay between batches. |
 |
|
|
Jampandu
Starting Member
12 Posts |
Posted - 2014-03-18 : 23:30:42
|
| I tried with batch size of 100 /1000/10000 in WHILE LOOP,but no luck. let me try with WAIT FOR delay and update youThanks! |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-19 : 15:28:27
|
Are you able to delete even one batch, without using any looping? Also, check if the OUTPUT INTO clause is causing any issues - perhaps the target table is blocking. You can run sp_who2 and see what is blocking what. Also, I prefer GO # rather than while loops for this sort of thing:DELETE TOP (10) FROM YourTable;GO 50 That will delete 50x10 = 500 rows from your table. |
|
|
|
|
|
|
UPDATE , DELETE result is blocking