2

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
Improve this question

1 Answer 1

Reset to default
7

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

Improve this answer
5
  • 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
    Commented Jan 23, 2013 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
    Commented Jan 23, 2013 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
    Commented Jan 23, 2013 at 12:59
  • yes, it is autogrowth 10% enabled, and it is currently at 70gb already...... will shrinking the ldf file help me?
    – grassbl8d
    Commented Jan 24, 2013 at 7:39
  • 1
    Why did it grow to 70GB? Is the recovery full and you're not taking log backups? You should a) identify the reason why it grew and mitigate the cause b) change the growth to a fixed amount (eg. 512MB) and c) shrink it to the actually necessary size
    – Remus Rusanu
    Commented Jan 24, 2013 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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