June 9, 2009 at 2:53 pm
We are having issues with a few queries users are running. The sql for these queries are quite large...may just be a poorly written query. But, the size of the entire db is about 35GB and it doesn't seem right that a query would require 15GB of space in tempdb.
The error we are getting is event id 1105 and has the following description:
Could not allocate space for object 'dbo.SORT temporary run storage: 140769735933952' 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.
Have any of you run into this? Do you have any suggestions?
I am looking at the estimated execution plan and the largest cost is coming from a clustered index scan which falls in line with the recommendation I have to create a missing index. I am doing some testing with this - but want to know if anyone has any suggestions.
June 9, 2009 at 4:33 pm
Update: I have now confirmed that the query is trying to utilized more than 64GB of tempdb data file.
June 9, 2009 at 5:16 pm
Can you post the query that is causing the problem? I would bet that it is because of the way the query is joined and/or how the query is built. There is probably a cross-join (or cartesian product) that is causing SQL Server to build huge working tables.
Could also be a triangular join - or anything else that is causing SQL to build very large work tables.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 10, 2009 at 1:49 pm
Jeffrey Williams (6/9/2009)
Can you post the query that is causing the problem? I would bet that it is because of the way the query is joined and/or how the query is built. There is probably a cross-join (or cartesian product) that is causing SQL Server to build huge working tables.Could also be a triangular join - or anything else that is causing SQL to build very large work tables.
Jeffrey,
Yes, the query is delivered via an application. There are 23 LEFT JOINs. I am going to test using between for the two times instead of >= and = '2009-04-15 00:00:00')
AND ( create_audit.audit_time <= '2009-05-22 00:00:00'))
AND ( (price_type_name) = @var1
OR (price_type_name) = @var2
OR (price_type_name) = @var3
OR (price_type_name) = @var4
OR (price_type_name) = @var5)
AND ( (bundle_name) = @var6)
AND (order_producer_id is NULL ))
GROUP BY order_number, order_contact.contact_first_name, order_contact.contact_last_name, order_customer.customer_organization_name, address.address_street, address.address_city, address.address_state, address.address_zip, order_contact.contact_phone_number1, order_contact.contact_phone_number2, order_contact.contact_phone_number3, order_contact.contact_email, section_name, price_type_description, gift_number, appeal.appeal_name, fundsplit_amount, gift_additional_information, order_due, order_comments, note_text) as nrows
[/code]
June 11, 2009 at 6:46 am
Are they really just counting on this huge select or did you add that for debugging or something?
June 12, 2009 at 10:05 am
andy russell (6/11/2009)
Are they really just counting on this huge select or did you add that for debugging or something?
The query is "as is" minus replacing a few strings with @var*.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply