Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rawilki
Starting Member
5 Posts |
 Posted - 2009-04-16 : 11:53:50
|
| SQL log shows I/O waiting over 15 seconds. Error message is:SQL Server has encountered 41776 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\SQLDatabases\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000808. The offset of the latest long I/O is: 0x00000186410000I notice most of these error messages relate to TempDB when DBCC CHECKDB is running.TempDB total size is 26.5GBI ran DBCC with ESTIMATEONLY option and found a database requires 30GB from TempDB.I assume there is a connection between TempDB being expanded during the DBCC execution.Is this resolution as simple as increasing TempDB or ??Thanks. |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-04-17 : 15:34:42
|
| It sure is. The database begins to increase in size, consuming resources, at the same time CHECKDB is consuming large amounts of resources. Increase tempdb as large as you can to accomodate the work load as opposed to simply letting autogrow perform the task. Autgrow should be left on for those times where you're not set accordingly, however, unless you don't mind the server scraching to a halt at some point. And be sure you have plenty of disk for growth of all your production DBs.Terry-- Procrastinate now! |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-18 : 00:00:48
|
| See this also:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83710 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-04-18 : 00:12:00
|
| Apart from the size of tempdb, the speed of the I/O subsystem on which tempdb is placed also needs to be considered.CHECKDB uses extensive storage in tempdb for reading and writing information about the database it's checking - if the disks are slow, then you'll see I/O waits - as you are. I suspect its a combination of growth and poor performance. See [url]http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Why-would-CHECKDB-run-out-of-space.aspx[/url] for more info.You should also turn on instant file initialization to ensure that the autogrowths don't result in I/O stalls. See [url]http://sqlskills.com/BLOGS/KIMBERLY/post/Instant-Initialization-What-Why-and-How.aspx[/url] for more info.How big is the database that you're checking?ThanksPaul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)SQL Server MVP, Microsoft Regional Director, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database course |
|
|
|
rawilki
Starting Member
5 Posts |
Posted - 2009-04-20 : 12:06:30
|
| Thanks to all for the replies.Paul, Database size is currently 123.5GB. Log file is 25GB. |
|
|
|
|
|
|
|
|
DBCC CHECKDB and TempDB