July 4, 2014 at 10:01 am
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:cool:
July 6, 2014 at 4:16 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2014 at 12:17 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply