Error: Could not allocate space for object ..because the primary filegroup is full

Question

Post reply

Error: Could not allocate space for object ..because the primary filegroup is full

sqlinterset

Ten Centuries

Points: 1341
More actions
November 19, 2015 at 9:35 am

#309710

I have a job that pulls data from teradata to SQL server 2012 through linked server. It's running since long and suddenly it got failed with above message. What could be the reason? Any help will be helpful

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

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

This is very vague question. Please copy/paste the error message in readable format that you are getting.

sqlinterset Ten Centuries Points: 1341 More actions · Answer 2

Error is: COuldn't allocate space for object 'XYZ: temporary run storage' in databas tempdb because the primary filegroup is full. Create disk space by creating unneeded files. SQLState 42000(error1105)

Lowell SSC Guru Points: 323518 More actions · Answer 3

the error is fairly straightforward, tempdb ran out of space.

Typically this is because tempdb auto expanded until it literally fill up the disk is is on.

That assumes you have one or more files for temp db all on the same drive.

it coudl also be that tempdb was limited to a specific max size, which would return the same error.

you will wnat to either get more disk space, or change your ETL to grab things in a more bite sized fashion;

linked servers featuring four part names, like SELECT * FROM Teradata.Database.schema.table need to be changed into OPENQUERY commands, maybe change it to grab smaller slices into a temp table, by date, or some other logical break.

four part calls like i mensioned, when joined to local data, do somethign you don't expect: it copies the ENTIRE remote table(s) from the linked server to local tempdb, THEN performs the joins behind the scenes, and then returns results; OPENQUERY can help alleviate the issue to a degree.

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!

sqlinterset Ten Centuries Points: 1341 More actions · Answer 4

Make sense... to check where it's using more space... i commented few of the parts of original job and executed on my local desktop matching.. it ran fine until it brings data to sql server (around 1 million) and it was very quick..after that i commented out... not i ran that job again which inserts data from staging table (where teradata data was kept) to main table by joining with other tables and group by clause.... it makes data around 25 million in size this step is actually taking more time...