I'm getting error 833 on my ldf file and i'm getting it a lot.
Please see the following. When this happens, my application is hang already.
I know error 833 has been reported a lot, but what does it mean if it is consistently pointing to my LDF file which it could not access?
Is it because the LDF file is already large?
I'm using SQL Server 2008 R2 on a windows 2008 environment.
The database is in a state in which it is unresponsive, a restart was the only that removed the hang I/O. Once it started, it didn't end already.
What's a possible explanation for this? Please see the log files in the sql error logs.
MIght it also be because of this bug?
http://support.microsoft.com/kb/2708012
Btw, the sql server was unresponsive the whole time, and this happened during the night time. Only the restart made the server responsive again.
01/21/2013 12:22:09,spid3s,Unknown,SQL Server has encountered 19 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6). The OS file handle is 0x00000000000008B8. The offset of the latest long I/O is: 0x00001028a8ac00
01/21/2013 12:15:29,spid3s,Unknown,SQL Server has encountered 17 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6). The OS file handle is 0x00000000000008B8. The offset of the latest long I/O is: 0x00001028c6ac00
Best Answer
Error 833 is not an 'error' per-se. It is reported because your IO subsystem is slow. the size of the LDF has probably nothing to do with it. It can be caused by many factors:
You need to investigate by looking at the drive load (Perfmon Physical Disk Object), at any OS reported errors for it (Event Viewer), check the drive SMART status. Also investigate the SQL IO load and how much goes to the problem drive, see sys.dm_io_virtual_file_stats