question

sadie avatar image
sadie asked

Error: "Could not allocate space for object in database 'tempdb'" in OPENQUERY execution

A SQL Agent job failed with the following error: "Could not allocate space for object '<object>' in database 'tempdb' because the 'PRIMARY' filegroup is full." when running a stored procedure with select OPENQUERY to a remote database. We added 7 more files in the tempdb filegroup of database where the agent job is running and spread out the space among the files and has a total of 240G to play with. Also, we are only expecting about 700K of data from the query result. After rerunning the job, it failed with the same error. Is there something else that we are missing?

Is it possible that the tempdb it is complaining about is the tempdb of the remote server and not server running the job?

sql-server-2008-r2tempdbfiles
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist ·

There's not enough information to answer the question.

1) What object can't fit? Do you just get the error message "Could not allocate space for object '<object>'...", or is there actually a name for <object> in the error message?

2) What does your query look like? If you just do a SELECT * FROM OPENQUERY, then your query will for sure be executed only on the remote side. But if you do something like SELECT * FROM OPENQUERY(....) WHERE blabla - then your local server will get a result set which is then filtered locally, which will use up space in TempDB.

So, as every so often - you need to provide better quality question if you want better quality answer :)

0 Likes 0 ·

1 Answer

·
sadie avatar image
sadie answered

Thanks for your response, @Magnus Ahlkvist. You have answered my last question on your 2nd point. The query is a SELECT * FROM OPENQUERY where it is running only on the remote side and it has multiple table joins. The remote server is controlled by the vendor. We will modify our code to break down the data extracted from the remote side.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.