asked on

Insert Timeout

I have an insert statement I am running from a .NET application, it runs through a stored proc.

It's a simple insert and it hangs and times out. I've set the timeout limit higher, and now it just runs and never completes. When I run it through Management studio with the same parameters it adds in 1 second.

I can't figure this out.

I think it's being blocked by something.

This brings the following results:
SELECT * FROM sys.dm_exec_requests;

57	2	2012-09-06 08:20:26.423	suspended	SELECT	0x0200000051224531F654C2B7F5206D6C811136D86C91C3810000000000000000000000000000000000000000	0	-1	0x06000700512245311058B9FF0100000001000000000000000000000000000000000000000000000000000000	7	1	ADE0AC9E-0E51-49D7-A5D8-D054A54D5D5B	0	ASYNC_NETWORK_IO	631	ASYNC_NETWORK_IO		0	1	399395	0x	0	0	0	102416	1	0x00000001F89D30C8	0	0	0	-1	us_english	mdy	7	1	0	1	0	1	1	1	1	2	-1	0	0	0	0	67224	0
119	0	2012-09-06 08:21:20.470	suspended	INSERT	0x03000700B982EE5A65A08001C3A0000001000000000000000000000000000000000000000000000000000000	1978	2732	0x05000700B982EE5AC03EAFE10100000001000000000000000000000000000000000000000000000000000000	7	1	9225A619-F1E3-44FE-BD5E-A3765832DCA4	57	LCK_M_IX	48370	LCK_M_IX	PAGE: 7:1:2333 	2	1	400422	0x	0	0	0	48371	3	0x00000001EFB16CF8	0	0	3	-1	us_english	mdy	7	1	0	1	0	1	1	1	1	4	-1	0	0	0	1	0	0

Open in new window

So I'm seeing suspended and LCK_M_IX on th Insert, so is this a user issue?

vasto

run sp_who2 and check BlkBy column to see if there are blocks

poweraddict

ASKER

blkby 58 on my insert statement

mastoo

58 is the spid of the blocking process, so also look in the sp_who2 for that spid. This leads to the user/system causing the block. In the simplest case, you ask them to disconnect or close their transaction. In the more complicated case, it is the same application as the insert and you need to study the blocking condition to come up with a solution.,

poweraddict

ASKER

For 58 I see my login name I use in my sql connection string from my .net application

58    SUSPENDED                      gsxraddict                                         BW-PC   .   PPDatabase SELECT           0       0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           234     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           218     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           218     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           187     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2

Open in new window

ASKER CERTIFIED SOLUTION

mastoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

poweraddict

ASKER

Thanks, I created a temporary table and it worked, I was modifying the table inside of a datareader.