June 1, 2013 at 4:50 am
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.SORT temporary run storage: 141509403869184' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
when I am executing a query with large tables , I am getting the above error message though I am not creating any temporary tables. I sharank the db and files but the problems persists.
Any suggestions .
Thanks in advance.
June 1, 2013 at 3:43 pm
seshagirikudaravalli (6/1/2013)
Msg 1105, Level 17, State 2, Line 2Could not allocate space for object 'dbo.SORT temporary run storage: 141509403869184' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
when I am executing a query with large tables , I am getting the above error message though I am not creating any temporary tables. I sharank the db and files but the problems persists.
Any suggestions .
Thanks in advance.
First of all do not shrink your databases anymore. http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/[/url]
The problem here is that tempdb needs some space for something you are doing and it doesn't have enough disk space. Do you have it set with a maxsize or unlimited growth?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2013 at 3:37 am
When you do a SORT action in a query, the sorting is executed in [tempdb]. Although you haven't declared any [tempdb] tables in your query, the query still uses [tempdb].
Check your available diskspace and the autogrowth settings (including max growth) of the [tempdb] to grow as much as needed.
When this is a regular action it's preferred to keep the [tempdb] at the size it will grow to. So you should adjust the initial file size accordingly. When the SQL service is restarted the [tempdb] will be rebuild with the correct size allready and autogrowth will default not be needed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply