May 2, 2007 at 11:00 pm
I am using SQL Server 2000 with ASP.NET 1.1
I add BLOB Data in the sql server. My system is working properly for single user. I got below error on my deployment server when multiuser are working. "Timeout expired. The timeout period elapsed prior to completion of the operation of the server is not responding."
I alreay set commandtimeout = 1200 form asp.net, connection timeout in connectstring. But no positive result of this change and got same error message.
When I add file with multiuser, then one user able to add file up to 100 MB, but other user are not able to add file and got the timeout error message. (concurrent usage problem).
During adding file, even other user cannot select data from that table with join operation. I feel there are some locking apply on that table which are not allowed to select data by other connection. I add BLOB data in the database chunk by chunk. Is there any problem of inserting chunk by chunk? Is it affect the lock system on database or table ?
I also add full text serach on that blob column. Is full text serach is affect on locking of the data ?
I also check it with removing full text serach and chunk by chunk insertion of data in BLOB field, but I got timeout error again.
Is there any way to handle any timeout problem in SQL Server. I am trying to resolve this problem since last 5 days.. I took hlep from so many friends.. but still I am not able to solve this.
I am hopeful from one of my friend and expect perfect solution.
Thanks in advance...
May 4, 2007 at 8:41 am
I think you problem is all about blocking.
What is your insert code doing? Is it ONLY doing an INSERT INTO or is it also trying to select from the table before INSERTING?
If you are doing an INSERT you will generate X locks that will last until the INSERT has finished. Which means that while One connection is doing INSERT another cannot do certain operations without being blocked until the INSERTing connection has finished it's business. You CAN (depending on your indexes,fragmentation,statistics and so on) do multiple INSERTs at the same time though (but operations that would potentially affect the row/rows being inserted will be blocked).
If you had 2k5 (and a fair bit of unused storage space) you could play around with SNAPSHOT isolation but if the topic header is correct (2k) you need to rethink a little more. One easy solution could be to 'extract' the BLOB data into a separate table.
Regards,
Hanslindgren
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply