This is going to sound strange, but its now happened to me four times.

I have the latest BVCommerce (with some additional modification by myself to handle multiple warehouses and server side on the fly image resizing). I'm running it on a powerful new 2005 web server, and I have SQL Server 2005, latest SP, running on a second server (quad proc, tons of memory), both in my colo.

I've got a really large catalog of products (4000+) and a bunch of categories (380).

Here's the issue : 4 times in the last 40 days (since I went live), the bvc_Product_ByCategory_s stored procedure will time out on every single execution made over the SQL Native Client. This happens on the cart (no products show on the category pages, effectively shutting down the cart), my development machine and any SQL Native Client connection to the database server. If I execute the sp on the server itself in the Enterprise Mgr, it runs and returns the result in less than a second.

The first 3 times this happened, I ended up having to fully reboot the SQL Server to resolve. The fourth time, I tried switching the connection string to try the OLEDB provider instead (which failed, of course) and then switched back, at which time it started working. I've now created a fully mirrored database on a second server and plan on trying to force failover on the next failure and see if that fixes it (I'm pretty sure it will).

Has anyone ever seen anything like this before? I find it mindboggling. Any hints on where to look for issues would be greatly appreciated.

By the way, the cart is here :
http://phoenixcigar.com/shop/

If you want to see the on the fly image resizing, take a look at the Cigar Brands page. All those images are a very wide variety of sizes on the server, but are scaled server side, with proportions intact. I had some serious issues with the built-in image resizing (partly because its done browser side, and partly because it didn't maintain proportions).

You won't see the warehousing from the customer side. When an order is placed, the order processor (person) checks stock at multiple warehouses, selects warehouses to ship each product (to condense shipping packages), and then the notices are handled like drop shipper notices. Inventory on the cart is summed across all warehouses.

That was the first thing I tried, but I also have the store running locally on my development machine, and when the online store stops getting a response for that query, my development machine fails as well. So it doesn't appear to be specific to the connection pool for the application. Also, recycling application pools on the web server does not fix the issue.

I'm sure its something on the database server, but I have no idea what it could be, or even where to look. The process locks look fine, the event log looks fine.

A workflow definitely can stop an order from being placed. The task you create to prevent the $0 shipping order from going through should be the first task in the place new order workflow.



Netriplex Corporation
Custom Bvc5 Development - "Helping to Build Your Store, Your Way"
Check out our "Tax in Product Pricing" module
http://bvc5demo.netriplexboston.com

Thanks, caplink, for your workflow suggestion (even if its for a problem in a different topic!). I'll try implementing that.

The SQL server is fully patched and is chugging along fine. This is the ONLY stored procedure that I've found to fail, but there could be others. Everything in the admin continues to work, and the site itself works, displaying the categories and recently viewed items just fine. But no products ever show when you click a category.

If I use the Visual Studio query tool (which uses SQL Native Client) and execute just that stored procedure with the bvin of a category, it times out. If I go to the SQL Server itself and execute the same combo, it runs and returns in less than a second.

So, yes, it looks like my next step is to use perfmon the next time it dies. I'm not all that good with it, but I'll see if anything jumps out at me.

Thanks, guys. I know this a weird issue.