🚀 go-pugleaf

Hi,

I have a very large query that includes multiple nested selects.

It works fine on my development machine, but in production (with large
volume) it fails because the Tempdb gets too large.  I added more space to
tempdb (now it has about 300mb).  The first time the query is invoked, it
works (tempdb used about 250mb).

However, the next time this query is executed, it seems that tempdb keeps
increasing.  The docs say that Tempb is initialized to the default
allocations (in my case 300mb) whenever sql server is started.

Is there a way of re-organizing tempdb back to its original state without
shutting down sql server ?

fyi here is my query:

CREATE PROCEDURE [m_spPrintInvoice] @ClientID integer, @EndDate datetime AS
if @ClientID < 1
Begin
SELECT M_Clients_Dta.ClientType, M_Clients_Dta.TaxId,
M_Clients_Dta.AccountStatus,
M_Clients_Dta.TaxExemptStatus, M_Clients_Dta.BillingPlanCode,
M_Clients_Dta.AccountName,
m_controldata_cod.description AS PlanDescription, M_Addresses_Dta.Street +
char(13) +M_Addresses_Dta.City + '   '+M_Addresses_Dta.State+ ' ' +
M_Addresses_Dta.Zip as ClientAddress, M_Clients_Dta.ClientID,
M_Addresses_Dta.EntityID, m_invoices_dta.invoiceid,
m_invoices_dta.PeriodStartDate,  m_invoices_dta.AdditionalText,
m_invoices_dta.PeriodEndDate, m_invoices_dta.OpeningBalance,
m_invoices_dta.TotalAmount,
PastDueText =
case
when m_invoices_dta.openingbalance < .01 then ''
else (SELECT Description from m_controldata_cod where controlgroup = 'IT'
and controltype = 'PD')
end
FROM M_Clients_Dta
Full Outer Join M_BillingPlans_Cod ON M_Clients_Dta.BillingPlanCode =
M_BillingPlans_Cod.BillingPlanCode
Full Outer JOIN M_Addresses_Dta ON M_Addresses_Dta.AddressType = 'BL' AND
M_Clients_Dta.ClientID = M_Addresses_Dta.EntityID
Join m_controldata_cod ON
m_clients_dta.billingplancode = m_controldata_cod.controltype and
controlgroup = 'BL'
Join m_invoices_dta ON m_invoices_dta.PeriodEndDate = @EndDate and
m_invoices_dta.clientid = m_clients_dta.clientid
end
else
Begin
SELECT M_Clients_Dta.ClientType, M_Clients_Dta.TaxId,
M_Clients_Dta.AccountStatus,
M_Clients_Dta.TaxExemptStatus, M_Clients_Dta.BillingPlanCode,
M_Clients_Dta.AccountName,
m_controldata_cod.description AS PlanDescription, M_Addresses_Dta.Street +
char(13) +M_Addresses_Dta.City + '   '+M_Addresses_Dta.State+ ' ' +
M_Addresses_Dta.Zip as ClientAddress, M_Clients_Dta.ClientID,
M_Addresses_Dta.EntityID, m_invoices_dta.invoiceid,
m_invoices_dta.PeriodStartDate,   m_invoices_dta.AdditionalText,
m_invoices_dta.PeriodEndDate, m_invoices_dta.OpeningBalance,
m_invoices_dta.TotalAmount,
PastDueText =
case
when m_invoices_dta.openingbalance < .01 then ''
else (SELECT Description from m_controldata_cod where controlgroup = 'IT'
and controltype = 'PD')
end
FROM M_Clients_Dta
Full Outer Join M_BillingPlans_Cod ON M_Clients_Dta.BillingPlanCode =
M_BillingPlans_Cod.BillingPlanCode
Full Outer JOIN M_Addresses_Dta ON M_Addresses_Dta.AddressType = 'BL' AND
M_Clients_Dta.ClientID = M_Addresses_Dta.EntityID
Join m_controldata_cod ON
m_clients_dta.billingplancode = m_controldata_cod.controltype and
controlgroup = 'BL'
Join m_invoices_dta ON m_invoices_dta.PeriodEndDate = @EndDate and
m_invoices_dta.clientid = m_clients_dta.clientid
Where m_clients_dta.clientid = @clientid
end


Thanx ... Lou
santlou@worldnet.att.net