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 |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2010-06-18 : 13:13:44
|
| Good morning.In a production system i've run a select query statement that joins multiple tables, and the main table is a huge table, its about a 700 millioms rows table.this a a user query and after running the query for about 3 o 4 hours yields the error:Could not allocate space for object 'dbo.SORT temporary run storage: 140737519747072' in database 'tempdb' because the 'PRIMARY' filegroup is full.Viewing in sqlserver logs while running the query, error messages often appear like:spid5s,Unknown,SQL Server has encountered 3152 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [r:\disk10\tempdev28.ndf] in database [tempdb] (2).It seems to be this query is not efficcient, isnt it? or what is the solution to avoid thies error message?I'd apprecieta your help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-18 : 13:39:16
|
| Your IO subsystem needs to be looked at. Even if your query is inefficient, your IOPS should not be taking that long. You'll need to get all the drivers and firmware up to date that deal with the IO subsystem. You should also work with the people responsible for the disks to determine if there is anything that can be done.Your error means you ran out of free space inside the tempdb data file. Either you ran out of disk space or your tempdb setting didn't allow it to expand. You can either address this by fixing the query or adding disk space to accomodate the query. You've really got to address your slow IO though as that's crucial.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Error in tempdb after running a query