Take the 2-minute tour ×
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.

When I alter or create a stored procedure directly on production or QA database, after a few seconds I start experience timeouts and application becomes unavailable.

Log files shows this error:

SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Database.ldf] in database [QA_Database] (9). The OS file handle is 0x0000000000000568. The offset of the latest long I/O is: 0x0000002821a200

We have SQL Server 2008 R2 installed, including latest Service Pack.

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.

share|improve this question
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-Smith Nov 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. –  Natalia Nov 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 Melton Nov 25 '11 at 3:08
add comment

migrated from stackoverflow.com Nov 16 '11 at 23:36

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

2 Answers

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.

Who knows I might get lucky on this one ;-)

share|improve this answer
2  
+1 I like your thinking, that's an educated shot in the dark. –  Mark Storey-Smith Nov 25 '11 at 0:42
add comment

You have an issue with the underlying IO subsystem for that database. It could be for a variety of reasons but the SQL Server is clearly struggling to read / write data.

You will be able to get more help on serverfault, and they will want the details about the IO subsystem to suggest metrics to check.

share|improve this answer
    
Thank you Andrew. We tested IO system. It works very well on production. Also, production server and staging are two complete different environments. –  Natalia Nov 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 –  Andrew Nov 16 '11 at 21:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.