I had a problem recently where a SqlCommand would work for weeks at a
time, and then start timing out. My SqlCommands always invoke stored
procedures, and the stored procedures always return the smallest amount
of data possible (usually only one row or using output parameters).
I had no idea what was causing the problem. The code hadn't changed. I
wracked my brain looking at the code, stepping through it, checking the
objects, viewing stuff in the debugger, blah blah blah. Everything
seemed peachy.
Then I pointed to a duplicate database on a different server. The code
executed without a hitch. I compared the stored procedures and
everything matched up. There was no problem there. After another few
weeks of searching for the cause of this mysterious error, we pinned it
down to one thing:
A monstrously huge transaction file.
Somehow, the SqlAgent on the development server had been shut down for
about a month (prolly by a wayward developer), and the backups weren't
running. Consequently the transaction log had reached a size in excess
of 15GB. Not surprisingly, the drive was down to 50MB free.
We reenabled the backup, ran it, and truncated the logs; everything ran
just beautifully after that.
If your SqlCommands start inexplicably timing out, check your
transaction log, and check the free space on the drive. It may not be
the cause of YOUR problem, but you'll be able to rule it out.
Hope this helps!