Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Could not allocate space for object temporary system object: ... in database 'tempdb' because the 'PRIMARY' filegroup is full. Expand / Collapse
Author
Message
Posted Friday, July 4, 2014 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, Visits: 113
Using SQL Server 2008 R2. Tempdb resides on disks W and X both of those are 250 GB each. Nothing else resides on these disks. The recovery mode for tempdb is set to Simple and is split in 8 files and one log file. Each of the tempdb file is set for auto growth by 128 MB and max size is unlimited. The log file is set for auto growth by 10% and max size unlimited.

I get the following error at least once a week while running an ETL:
[SQLSTATE 01003] (Message 8153) Could not allocate space for object '<temporary system object: 335532118192937>' 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. [SQLSTATE 42000] (Error 50000). The step failed.

I have to then shrink the tempdb log and files to create space on the disks.

What is a permanent solution to this problem, I don't want my ETL to break due to TempDB diskspace full.

Thanks in advance.


Forum User
Post #1589416
Posted Sunday, July 6, 2014 4:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
Either get a lot more space for the thing so it doesn't fill the drive, or, look into your ETL process and determine what is allocating so much space into tempdb and break that into smaller pieces so it allocates less.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1589615
Posted Sunday, July 6, 2014 12:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
ForumUser3 (7/4/2014)
Using SQL Server 2008 R2. Tempdb resides on disks W and X both of those are 250 GB each. Nothing else resides on these disks. The recovery mode for tempdb is set to Simple and is split in 8 files and one log file. Each of the tempdb file is set for auto growth by 128 MB and max size is unlimited. The log file is set for auto growth by 10% and max size unlimited.

I get the following error at least once a week while running an ETL:
[SQLSTATE 01003] (Message 8153) Could not allocate space for object '<temporary system object: 335532118192937>' 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. [SQLSTATE 42000] (Error 50000). The step failed.

I have to then shrink the tempdb log and files to create space on the disks.

What is a permanent solution to this problem, I don't want my ETL to break due to TempDB diskspace full.

Thanks in advance.


It would appear that you're saying that TempDB has been allocated a total of 500GB. That's a fair bit of space for TempDB.

The first thing I'd do is change the growth setting for the TempDB log from 10% to some fixed byte size, say 1GB or some such. I'd also change the growth setting for the mdf/ndf files from 128MB to at least 1GB.

The next thing to do is to find out what is causing the explosive growth of the Log file. These types of things typically happen because someone doesn't understand the data they are working with and probably wrote some god-awful monster single query with one or more "many-to-many" joins (otherwise known as "accidental CROSS JOINs) in the criteria and tried to overcome those issues with the use of either DISTINCT or GROUP BY instead of using more proper criteria and "Divide'n'Conquer" methods.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1589684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse