February 15, 2011 at 6:12 am
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
February 15, 2011 at 6:28 am
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 errorCould 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
February 15, 2011 at 11:13 pm
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.
February 16, 2011 at 2:39 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply