File size question

[dobe1]

Upon executing this code:
select a.ccmpny, b.rdrout
from cust_table a join rdtl_table b on a.ccmpny + cast(a.ccust# as char) = b.rdcomp + cast(b.rdcust as char)
where a.ccmpny in ('93', '95') and a.ccycle <> 'cr'
group by a.ccmpny, b.rdrout
order by a.ccmpny, b.rdrout


I receive the following error.
Server: Msg 1101, Level 17, State 10, Line 2
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

My DBA increased the size of the database, but to no avail.

Do any of you has a solution for this problem?

Thank you in advance.

Dobe

 

[gradley]

I know it's difficult to determine how much space TEMPDB may need for a particular script so if the drive that TEMPDB resides on has plenty of available space, you may want to have the DBA increase TEMPDB to a very last size.

Unfortunately when a script fails because of lack of space in TEMPDB, the transaction rolls back from TEMPDB and it appears that you have plenty of space left.

 

[Pattycake245]

Is it because you are referencing a column with a number sign in it(#)? Put square brackets around that column and try.

Upon executing this code:
select a.ccmpny, b.rdrout
from cust_table a join rdtl_table b on a.ccmpny + cast(a.[ccust#] as char) = b.rdcomp + cast(b.rdcust as char)
where a.ccmpny in ('93', '95') and a.ccycle <> 'cr'
group by a.ccmpny, b.rdrout
order by a.ccmpny, b.rdrout

Tim

 

[dobe1]

gradley,
I have requested more TEMPDB space (again) per your advice.

Tim,
It isn't the # symbol. The above code will run, if I remove the group by statement.

 

[dobe1]

I still need help. The last email I received from my DBA is "I grew the database some more. See what we get now. I have both of them
Soft_Pak and Tempdb set at 30% now." I am still receiving the same error message listed above.

Dobe

 

[gradley]

I have a few questions:

1. Roughly how many rows in each table of the join?
2. How long before the error is raised?
3. Have you watched TEMPDB as the script is running to see if it reaches a maximum point before this error is raised?
4. Do you think the CAST function may be creating spaces in the company + customer concatination? Have you tried an LTRIM() on the Cast?

 

[JamesLean]

Am I right in thinking you need to join the tables on two columns, which is why you are concatenating the values? If so, try rewriting it like this (also note the use of DISTINCT rather than GROUP BY):

SELECT DISTINCT a.ccmpny, b.rdrout
FROM cust_table a
	JOIN rdtl_table b ON a.ccmpny = b.rdcomp
		AND a.ccust# = b.rdcust
WHERE a.ccmpny IN ('93', '95')
	AND a.ccycle <> 'cr'
ORDER BY a.ccmpny, b.rdrout

--James

 

[dobe1]

gradley,

1. Roughly how many rows in each table of the join?
532,000 records in the cust_table
1,073,000 in the rdtl_table

2. How long before the error is raised?
Approximately 5 seconds or slightly longer.

3. Have you watched TEMPDB as the script is running to see if it reaches a maximum point before this error is raised?
No. Please inform me how to do so.

4. Do you think the CAST function may be creating spaces in the company + customer concatination? Have you tried an LTRIM() on the Cast?
It is possible, but that would suggest that the join would not work, correct? The join itself works, based upon a test with the same code , but without the "group by".

I am wondering, if maybe there is simply not enough disk space. I will check with my DBA.


JamesLean,
The concatenated values are composite keys. I chose to use a group by, because I will be adding aggregates to the code once this code works.

Thank both of you for your efforts,

Dobe

 

[SQLBill]

Your use of the term FILEGROUP has me wondering. One thing that can be done with SQL Server is to organize database files together into FILEGROUPs. I've never done this, so I really can't help with it if this is what the issue is.

Check with your DBA to see if they organized the files into filegroups.

For more information, refer to the BOL, use the Index tab and enter FILEGROUPS, select the subtopic Described. Scroll down the right window to DATABASE FILEGROUPS.

-SQLBill

Posting advice: FAQ481-4875

 

[gradley]

Dobe,

Having a space on both concatinated values will still join successfully but is not very efficient. JamesLean brings up a good suggestion about joining the fields separately rather than concatination.

Have you pasted this script into Query Analyzer and run the Execution Plan against it? I'm curious if there is a bottleneck in the GROUP BY or ORDER BY clause. This would help to identify that part that's taking the longest. Try it with and without the GROUP BY and or ORDER BY.

Based on the number of records you are joining, it wouldn't surprise me if TEMPDB is filling up. SQLBill's FILEGROUP concern is also worth looking into with the DBA.

As for viewing the TEMPDB, you can do that using the Task Pad view in Enterprise Manager. Keep hitting F5 to refresh.

 

[dobe1]

Thanks all. I will spend the rest of the evening
analyzing these suggestions.

Dobe

 

[dobe1]

Situation Resolved.

It seems that the log file was alowed to run wild, and
grow out-of-control.

I still wish to thank each of you who helped with this matter.

Dobe

 

[gradley]

Anytime...

Glad to hear you've resolved the issue.