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.