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 12»»

Tempdb Space Issues Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 2:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Hi there

When running an SSIS package I get the following message:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 140748109840384' 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.".

I have checked disk space - plenty of it left
Tempdb is set to "autogrow"

Yet - still get this error.

Any ideas?

Regards
Chris
Post #1444607
Posted Friday, April 19, 2013 2:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Chris Hewitt-261101 (4/19/2013)
Hi there

When running an SSIS package I get the following message:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 140748109840384' 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.".

I have checked disk space - plenty of it left
Tempdb is set to "autogrow"

Yet - still get this error.

Any ideas?

Regards
Chris


How much space is available on the drive where tempdb resides?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444608
Posted Friday, April 19, 2013 2:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
You may want to look at what it is your process is doing and with how much data. If my swag is correct you need 131,081+ GB of disk space for the sort.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444609
Posted Friday, April 19, 2013 2:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Hi Lynn

17gb of disk space left, so don't surely space cannot be the issue then?

This is on a client old server - only 3.75 gb of ram. Could this be the issue?


Thanks for you help!

Regards
Chris
Post #1444617
Posted Friday, April 19, 2013 2:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Chris Hewitt-261101 (4/19/2013)
Hi Lynn

17gb of disk space left, so don't surely space cannot be the issue then?

This is on a client old server - only 3.75 gb of ram. Could this be the issue?


Thanks for you help!

Regards
Chris


Based on the following:



"Could not allocate space for object 'dbo.SORT temporary run storage: 140748109840384' in database 'tempdb' because the 'PRIMARY' filegroup is full.



If 140748109840384 is the number of bytes needed, that computes to a bit under 131,082 GB of space needed.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444618
Posted Friday, April 19, 2013 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Mmm doesn't make sense

Seems to be happening on a table where I import 3.5 million rows, which is not a lot. Have watched tempdb space, and it remains at 8mb all the way through.

Disk space remains 17 gb
Post #1444622
Posted Friday, April 19, 2013 3:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
As I said, you may want to look at the process and the data. It looks like it thinks it needs 130000 GB of disk space to run a sort.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444625
Posted Friday, April 19, 2013 3:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 17,948, Visits: 15,944
How does the tempdb log look?

Is it set to autogrow as well?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1444626
Posted Friday, April 19, 2013 3:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 17,948, Visits: 15,944
How much free space is in tempdb data files?

How much free space in in the tempdb log file?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1444627
Posted Friday, April 19, 2013 4:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Hi Lynn

Does this number definitely represent the number of bytes needed?

Chris
Post #1444631
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse