SQL Tempdb utliizing high disk resources - heavy read / writes

Posted on 2011-10-27
Last Modified: 2012-05-12
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:]

Thanks for any thoughts.
Question by:zpomo
    LVL 7

    Assisted Solution

    TempDB optimization requires some good analysis to really get to the root of the problem.  In fact, Jeremiah from Brent Ozars team just blogged about this today and has a webcast you should watch next tuesday on this same issue.  However, this does not help you right this second.

    -How big is your main database, and how big is TempDB when you get these errors, or right now?  How many CPU cores do you have?  Do you have any baseline metrics available to view?  Let us know, thanks.
    LVL 7

    Expert Comment

    Oh also, can you check your job history to see if a certain job is kicking off at that point?  Do you have any proficiency in SQL Profiler to profile the server to see what's going on at that moment?
    LVL 7

    Accepted Solution

    You're seeing several different messages - the biggest problem I see is the i/o subsystem not being fast enough for SQL. WIth SQL 2000 SP4 the i\o messages started to be printed in the SQL server errorlog. i/o is expected to complete within 15 ms or less - the i/o messages will appear at a factor 1000 times behind the expectations. Make sure your incremental file growth is not any longer set to 10% which can become devastating on slow i/o systems with large incremental file growth. Set it to maybe 100 MB at a time to not over run the i/o capacity of your storage system.
    I/o being frozen is typically a message you see when a form of backup software is executing. Connections not being accepted right after a SQL restart is also fairly normal, SQL may take another couple of moments to be ready to accept them again. The line to look for is recovey complete to signal a successful SQL start.
    LVL 30

    Assisted Solution

    by:Mike Lazarus
    What version of ACT!? Full build from Help | About...
    LVL 68

    Expert Comment

    Do you have multiple tempdb *data* files? (Don't need, or want, multiple log files).

    Are they sized large enough to prevent autogrowth?

    Make sure you don't use *any* "%" growth, specify a fixed amount.

    Make sure you've enable IFI (instant file init.) so that SQL does not have to waste time pre-formatting autogrow data additions (just in case).

    Make sure the log file is sized large enough so that it does not need to autogrow -- this is *vital*, because a log file *must* be preformatted before use and it will stop activity in tempdb while it occurs.
    LVL 7

    Expert Comment

    Did you manage to reqwork your disk subsystem to give SQL more i/o bandwidth or more memory?
    How large is the ACT db anyway?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Somehow I don't think the author cares anymore...
    LVL 1

    Author Comment

    Thank you all for your input a few months back.
    and I apologize- i neglected to follow up on this question.    It is my first post and I was getting so much email from EE i forgot some of it was actually about MY issue.  

    in the end -- (going back a few months now)  we rebuilt the server -- and added new disks,  - better configured for SQL  (3 seperate physical disks to handle faster read /writes.

    interestingly,  We had imaged the machine and onto a temp server and had it running in production,  and the issue did not recur.   (the drive configuration was not that different on the temp server as it was on the production machine..  i think we must have had some drive issue.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Excellent!  Please close the question.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now