TEMPDB issue.....

Question

TEMPDB issue.....

AppSup_dba

SSCrazy

Points: 2215
More actions
February 15, 2011 at 6:12 am

#231390

I am running a insert query that joins some 6 tables and fetches 3LAKH rows into another table. My TEMPDB size more than 40GB. I am still getting error
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The ISSUE is when i check the growth of TEMPDB using
USE tempdb
GO
EXEC sp_spaceused
the usage of TEMPDB is continuously shows as below
database_namedatabase_sizeunallocated space
tempdb41992.63 MB41939.50 MB
reserveddataindex_sizeunused
2688 KB784 KB888 KB1016 KB
So my question is when the TEMPDB isnt full, why the query throwing up this error???
Cheers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

AppSup_dba (2/15/2011)
I am running a insert query that joins some 6 tables and fetches 3LAKH rows into another table. My TEMPDB size more than 40GB. I am still getting error
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The ISSUE is when i check the growth of TEMPDB using
USE tempdb
GO
EXEC sp_spaceused
the usage of TEMPDB is continuously shows as below
database_namedatabase_sizeunallocated space
tempdb41992.63 MB41939.50 MB
reserveddataindex_sizeunused
2688 KB784 KB888 KB1016 KB
So my question is when the TEMPDB isnt full, why the query throwing up this error???

the error to me implies that tempdb needs to grow larger than the 40 gig...possibly due to your query witht eh big temp table:

tempdb 41992.63 MB 41939.50 MB <--THAT says it is using 41 + gig...free space is around 53.13 Mb(41992.63 - 41939.50 )

what is the max size you've assigned to tempdb?

better yet...what is your query...it is quite possible that the work you want to do does not require a temp table at all., which would save you a lot of work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

baabhu SSCertifiable Points: 6205 More actions · Answer 2

You can use a Permenant temp table to insert all the (3 Lakh records) and see what is the space taken for the table.

That gives an idea of what is the space is required in the temp table.

To me using more than 40 GB is something you need to look about avoid using tempdb.

rod.means Old Hand Points: 311 More actions · Answer 3

You could always try running the query below and see how much free space is returned.

SELECT SUM(unallocated_extent_page_count) AS [free pages] ,

( SUM(unallocated_extent_page_count) * 1.0 / 128 ) AS [free space in MB]

FROM sys.dm_db_file_space_usage ;

This is assuming that you are on SQL 2005 and above.

Also you may want to check your auto growth setting.