tempdb grows and full - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

tempdb grows and full

Author

Topic

raagi2000
Starting Member

6 Posts

Posted - 2005-12-07 : 10:55:41

tempdb grows suddenly and fills up while running this.. any suggestion would be appreciated. v_position_history_cashest refers to muliple views and finally base tables. there is no index on v_position_history_cashest.account_id. i am not sure if index on the column on the view can help. its is sybase ASE 12.5.

select s.security_id,
position_date = max(ph.position_date),
a.account_id,
account_name = a.name_1,
security_identifier = sc.identifier,
security_name = rtrim(s.name_1) + ' ' + rtrim(s.name_2),
shares =max(ph.trade_date_quantity),
market_value = max(ph.market_value),
coupon = max(s.income_rate),
maturity_date = max(s.maturity_date)
into #final
from v_position_history_cashest ph,
account a,
account_classification ac,
security s,
security_classification sc
where ph.position_date = @p_end_date
and ph.account_id = a.account_id
and ph.account_id = ac.account_id
and ph.security_id *= sc.security_id
and ph.security_id = s.security_id
and ac.account_type_code in
('BD','BI','ED','EI','MD','MI','PA','PF') -- excludes II, ID, NO
group by a.account_id, sc.identifier

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-07 : 11:58:25

"its is sybase ASE 12.5."
....hmmmm....most stuff here deals with MSSQL!

as an aside...indices will speed things up, but should not have an influence on the size of tempdb....any chance tempdb (or it's drive) is to small???

raagi2000
Starting Member

6 Posts

Posted - 2005-12-07 : 12:02:56

tempdb is more than 1 gig.

Kristen
Test

22859 Posts

Posted - 2005-12-07 : 13:39:29

What is:


SELECT COUNT(*)
from v_position_history_cashest ph,
account a,
account_classification ac,
security s,
security_classification sc 
where ph.position_date = @p_end_date
and ph.account_id = a.account_id
and ph.account_id = ac.account_id
and ph.security_id *= sc.security_id
and ph.security_id = s.security_id
and ac.account_type_code in 
('BD','BI','ED','EI','MD','MI','PA','PF') -- excludes II, ID, NO
group by a.account_id, sc.identifier

that would give an indication of how much data will be stored in TEMPDB.

Also, it used to be the case that

SELECT ...
INTO #xxx
FROM ...

used to lock TEMPDB for the duration of the query; MS have improved this, and I assume Sybase too, but maybe pre-CREATE'ing the #table would exert less strain.

Maybe there are also Sub-Selects in the VIEWs that themselves require elbow-room in TEMPDB. If so you might have more luck by pre-selecting subsets of data from the Views, and then joining those instead of the actual VIEWs.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources