Hey all,

I am haunted by issues with our imaging database. I thought I had it fixed when I redesigned the database and did a complete system rewrite, but an error from the past returned today.

At certain times, the Image table stops accepting image inserts. We store images in an "image" data type field by using ADO. The ADO stuff is very standard... Call the AddNew method, set the fields (using CREATEBINARY(FILETOSTR([image file])) to stream data to the image field), and then call UpdateBatch to save everything. Works slick, and then we also have access to the auto-generated primary key for the newly added record. Almost 2 million images have been placed in our database in this fashion, not to mention using that same methodology when we converted to the new data model 3-4 months ago.

Today we started getting timeouts (even when the timeout period was set to 2 minutes) at the point of the UpdateBatch() command. I was even able to reproduce in the debugger. Database looked fine, had free space in log and data files, but saving to the image field would time it out. I could insert records without the image field filled in, and it worked absolutely fine. Keep in mind, when working, I can insert images at the rate of several per second, so a timeout of 2 minutes means something is gravely in error.

Then I tried something funky. I started saving bigger and bigger pieces of data into the image field using CREATEBINARY(REPLICATE("1", [ever-increasing number])). I could save data 100 bytes long, then 1000, but when I tried saving 10000 (~10K of data) to the image field, UpdateBatch would start hanging again. We are talking about it going from an instantanseous save to exceeding a two-minute time out.

We rebooted the SQL server entirely. No effect. On a lark, I upped the log file size manually (from 15 GB to 20 GB). No help. I then upped the data file size manually from 100 GB to 120 GB. That took a while, about 20-30 minutes. Eureka! I was able to insert full images once again.

Sure, "eureka", except I am no closer to understanding what happened here. The data file has always been set to automatically expand when it needs to. I know that when it auto-expands, things do get a lot slower, and even timeout during the expansion "thrash". But I was working on the error at least an hour before we did the reboot, which would have been plenty of time for SQL Server to have auto-expanded from the time we encountered our first error. It didn't. And yet, a manual expand fixed the problem.

I have no problem having to manually expand. But I was wondering if anyone else has seen issues like this. Any workarounds, best practices, "gotchas" that you can pass on to me? I desperately want to learn all I can, as there is nothing worse than when a perfectly-running "black box" stops working for no apparent reason.

Any and all help would be greatly appreciated...

Thanks,
Joe Kaufman