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.

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

Thanks!

share|improve this question
add comment

1 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:

  • an inherently slow drive
  • a flaky hardware that masks errors with many retries (typical of consumer grade drives)
  • the drive is experience more load that it can bear

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

share|improve this answer
1  
+1 nice answer. –  Mitch Wheat Jan 23 '13 at 11:13
    
hi thanks for the answer, however, what we don't understand is after restarting the database, the errors were gone. why did it need a restart? It also happened during the time that no one was using the system. –  grassbl8d Jan 23 '13 at 11:59
    
the question i have is, why did the SQL Server restart suddenly solve the issue? and why was it only pertaining to the LDF file? Not any other files, I think there was some sort of issue with my LDF file. Is it because it has grown very large? –  grassbl8d Jan 23 '13 at 12:15
2  
Does your LDF has auto-growth by 10% enabled? A large LDF (eg. 200GB) will grow by 10% triggering a 20Gb zero-fill, which can be very demanding on the disk. First of all the log should not grow, truncation should occur, see rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log to understand why it grows. Even if it grows, it should be configured to grow on fixed amounts, not by 10%. So a large log + auto-grow may cause log write pressure, but that only exposes the real problem: slow disk. –  Remus Rusanu Jan 23 '13 at 12:59
    
yes, it is autogrowth 10% enabled, and it is currently at 70gb already...... will shrinking the ldf file help me? –  grassbl8d Jan 24 '13 at 7:39
show 1 more 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.