SQL Tempdb utliizing high disk resources - heavy read / writes
Posted on 2011-10-27
we have 50 LAN users accessing a shared ACT database (SQL 2008 standard backend) hosted on a dedicated Win 2008 server. (Raid 5 w/ 4 drives, and 18gb RAM)
every 4 - 6 hours the server will lock up becuase of heavy hard-drive read / writes being performed by the SQL tempdb.
In the events log, we see these errors that correspond to this behavior:
SQL Server has encountered 97 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10_50.ACT7\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000560. The offset of the latest long I/O is: 0x00000001650000
Event id: 18056
The client was unable to reuse a session with SPID 107, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Event id: 3197
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Event id 17187
SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 192.168.2.104]
Thanks for any thoughts.