• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6569
  • Last Modified:

SQL Tempdb utliizing high disk resources - heavy read / writes

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:
833
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.
0
zpomo
Asked:
zpomo
  • 2
  • 2
  • 2
  • +3
3 Solutions
 
MrAliCommented:
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.
www.brentozar.com

-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.
0
 
MrAliCommented:
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?
0
 
skaraiCommented:
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.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
What version of ACT!? Full build from Help | About...
0
 
Scott PletcherSenior DBACommented:
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.
0
 
skaraiCommented:
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?
0
 
Anthony PerkinsCommented:
Somehow I don't think the author cares anymore...
0
 
zpomoAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
Excellent!  Please close the question.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now