Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to reproduce it on QA, but to no avail. I have no clue what it could be.

Stored procedure execution time is around 1 second and it runs occasionally. It returns just few records. It happened with 2 different stored procedures and I think that changing, not running, a stored procedure triggered something that caused the issue.
Once I deleted it, issue was gone immediately.

This question came from our site for professional and enthusiast programmers.

1

Is this a very large or complicated stored procedure? What would be its typical execution time? How frequently is it called (10s of calls per second, hundreds per second)? How large is the database? How much traffic is the server dealing with?
–
Mark Storey-SmithNov 16 '11 at 23:45

Stored procedure execution time is around 1 second and it runs occasionally. It returns just few records. It happened with 2 different stored procedures and I think that changing, not running, a stored procedure triggered something that caused the issue. Once I deleted it, issue was gone immediately.
–
NataliaNov 17 '11 at 15:16

Well the first issue I see on this is that you have the database on the "C:" drive of your server. That is the same drive your OS is on running. Simply moving the database files off to another drive might fix your problem.
–
Shawn MeltonNov 25 '11 at 3:08

2 Answers
2

Just a hunch... is that ldf growing when you get this error? I seen this error a few times and always been that someone set the growthrate in too big steps for the io subsystem to handle at same time as normal io within 15 seconds. Go and see if it happens to bet set to 10% and how big a step that would be. If it is GBs you might want to set it to a smaller size to grow and also in MB instead of percent.

Thank you Andrew. We tested IO system. It works very well on production. Also, production server and staging are two complete different environments.
–
NataliaNov 16 '11 at 21:41

2

There are some blogged troubleshooting guides : blogs.msdn.com/b/chrissk/archive/2008/06/19/… The problem though is going to be configuration / hardware in some form, and not programming - thus serverfault is the ideal community to ask
–
AndrewNov 16 '11 at 21:43