Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query which was earlier fine but now it is running me out of hard disk space with message that tempdb is full. My Harddisk has 90 gb of space as soon as i run query temdb runs out of space in 2 hours.They query runs for period of 7 months data. I have reduced the duration to even 1 day it is still runs out . Please guide me.

If required i can post the query as well. but query is very lengthy.

DECLARE @StartDate AS date,
        @EndDate AS Date
SET @StartDate='2013-04-01'
SET @EndDate='2013-10-31' 
while @StartDate<=@EndDate BEGIN 
print @StartDate
SELECT ROW_NUMBER()over(partition BY b.regno
                        ORDER BY cast(CASE WHEN postdate IS NULL
      OR postdate='1900-01-01'THEN LedgerDate ELSE PostDate END AS DATE))SRNo,
         CASE
             WHEN d.CrsCategoryDashBoard in('Admission cum scholarship test','ANTHE','MISC','LIBRARY','Transport Course') THEN b.LRTStatus
             ELSE CASE
                      WHEN b.LRTStatus NOT in(0) THEN CASE
                          WHEN dbo.GetStudentstatusOnDate(b.RegNo,@StartDate)IS NULL THEN b.LRTStatus
                          ELSE dbo.GetStudentstatusOnDate(b.RegNo,@StartDate)
                      END
                      ELSE b.LRTStatus
                  END
         END AS LRTStatus ,
         b.regno,
         /*b.StudentName*/
         '' AS studentName,
         c.InstAmt ,
         cast(round(c.InstAmt*e.TotalServiceTax/100,0)AS decimal(18,2)) serviceTax ,
         e.TotalServiceTax,
         isnull(allottedConcession/ CASE WHEN e.TotalServiceTax=0 THEN 1 ELSE (1+(e.TotalServiceTax /100)) END,0) allottedConcession,
         isnull(allottedLumpsump/CASE WHEN e.TotalServiceTax=0 THEN 1 ELSE (1+(e.TotalServiceTax /100)) END,0) allottedLumpsump ,
         isnull(a.BaseAmount,0)Paidamount,
         isnull(a.ServiceTax,0)PaidServiceTax ,
         isnull(a.Concession ,0)- (isnull(a.Concession ,0)/(1+((c.InstAmt*e.TotalServiceTax/100)/CASE WHEN BaseAmount=0 THEN 1 ELSE BaseAmount END)))PaidConcession,
isnull(a.LumpSump,0) AS PaidLumpsump ,
cast(CASE WHEN postdate IS NULL
     OR postdate='1900-01-01' THEN LedgerDate ELSE PostDate END AS DATE) AS RealisationDate,
isnull(a.PayModeType,'') AS PaymodeType,
isNull(a.PaymentStatus,'') AS PaymentStatus ,
c.CourseName,
CASE
    WHEN InstType IS NULL THEN CASE
   WHEN c.InstNo>1 THEN 'I'
   WHEN c.InstNo=-1 THEN 'A'
   WHEN c.InstNo=-2 THEN 'R'
                               END
    ELSE InstType
END AS Insttype,
c.InstNo,
d.CrsCategoryDashBoard,
b.regdate,
c.Instdt,
c.startdt,
cast(CASE WHEN d.CrsCategoryDashBoard IN ('Admission cum scholarship test','ANTHE','MISC','LIBRARY') THEN b.RegDate ELSE CASE WHEN D.CrsCategoryDashBoard='Transport Course' THEN CASE WHEN c.Instno=-2
     OR c.Instno=-1
     OR c.Instno=1 THEN b.RegDate ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END ELSE CASE WHEN c.Instno=-2 THEN b.RegDate WHEN c.Instno=-1
     OR c.Instno=1 THEN CASE WHEN RegDate>C.StartDt THEN RegDate ELSE c.StartDt END ELSE --In case student is transferred the regdate will be greater than installment date
CASE WHEN RegDate>C.StartDt THEN CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END END END END AS date) AS InvoiceDate ,
acct_type ,
Acc_CenterCode
FROM tbl_RegMaster AS b
LEFT JOIN vw_CrsDetail AS c ON SUBSTRING(b.regno,1,3)+substring(b.regno,9,8)=c.crsCode
LEFT JOIN Payment_Ledger_Matching AS a ON a.Regno=b.RegNo
AND CASE
        WHEN a.InstType='R' THEN -2
        WHEN a.InstType='A'THEN -1
        WHEN a.InstType='I' THEN a.InstNo
    END= c.InstNo
LEFT JOIN tbl_CrsCategory AS d ON c.CrsCategory=d.CrsCategory
AND c.Acct_Type=d.Stream
AND c.CrsCategoryYear=d.year
INNER JOIN tbl_Stax AS e ON c.staxid=e.STaxID
AND @StartDate BETWEEN e.FromDate AND e.ToDate --and IsCurrent=1
LEFT JOIN temp_ConcessionDetails AS f ON b.regno=f.RegNo AND c.InstNo=f.InstNo
JOIN tbl_CenterDetails AS g ON a.CenterId=g.CenterID
WHERE substring(a.REGNO,1,3) IN (some center code they are around 60)
AND SUBSTRING(c.crscode,5,8) IN (some course code which are around 200)
 AND cast(CASE WHEN d.CrsCategoryDashBoard IN ('Admission cum scholarship test','ANTHE','MISC','LIBRARY')
               THEN b.RegDate 
               ELSE 
                    CASE WHEN D.CrsCategoryDashBoard='Transport Course' 
                    THEN 
                        CASE WHEN c.Instno=-2 OR c.Instno=-1  OR c.Instno=1 
                        THEN b.RegDate 
                        ELSE 
                            CASE WHEN b.RegDate>Instdt 
                            THEN RegDate 
                            ELSE Instdt 
                            END 
                        END 
                    ELSE 
                        CASE WHEN c.Instno=-2 --RegistrationTHEN b.RegDate WHEN c.Instno=-1         OR c.Instno=1 --Admission and First Installlment
                        THEN 
                            CASE WHEN RegDate>C.StartDt 
                            THEN RegDate --'2013-04-04'
                            ELSE c.StartDt 
                            END 
                        ELSE --In case student is transferred the regdate will be greater than installment date
                            CASE WHEN RegDate>C.StartDt 
                            THEN 
                                CASE WHEN RegDate>@StartDate 
                                THEN RegDate 
                                ELSE Instdt 
                                END 
                            ELSE 
                                CASE WHEN b.RegDate>Instdt 
                                THEN RegDate 
                                ELSE Instdt 
                                END 
                            END 
                        END 
                    END 
                END AS date) =@StartDate
AND c.CourseName NOT LIKE '%library%security%'
SET @StartDate=DATEADD(dd,1,@StartDate) 
END

It also uses one recurive function

which is as below

ALTER Function [dbo].[GetStudentstatusOnDate](@regno as varchar(30),@dated as date )
returns int
as
begin
DECLARE @status as int
;WITH t1 AS
  (SELECT top 100 percent rank()over(ORDER BY dated, Lrtstatus*-1) rowN, RegNo, dated, LRTStatus FROM
     (SELECT RegNo,cast(regdate AS DATE) AS dated, CASE WHEN LRTStatus=3 THEN LRtstatus ELSE 0 END AS LRTStatus
      FROM tbl_RegMaster AS a
      WHERE a.stdkey in
          (SELECT stdkey
           FROM tbl_RegMaster
           WHERE regno=@regno)
        AND (CourseName NOT LIKE '%scholarship%'
             AND CourseName NOT LIKE '%charge%'
             AND CourseName NOT LIKE '%LIBRARY%'
             AND CourseName NOT LIKE '%national%'
             AND CourseName NOT LIKE '%transport%'
             AND CourseName NOT LIKE '%service Tax%')
      UNION SELECT RegNo,
                   cast(LeftRejoinTransferDate AS date) AS dated,
                   Status AS LrtStatus
      FROM tbl_LeftRejoinDetails
      WHERE stdkey in
          (SELECT stdkey
           FROM tbl_RegMaster
           WHERE regno=@regno)
        AND SUBSTRING(regno,10,7) NOT LIKE 'TP%'
        AND SUBSTRING(regno,10,7) NOT LIKE 'LB%') AS FINAL
   ORDER BY dated,
            LRTStatus DESC)
SELECT @status=min(LRTStatus)
FROM t1
WHERE @Dated BETWEEN cast(dated AS date) AND cast(CASE WHEN
                                                    (SELECT MAX(dated)
                                                     FROM t1 AS a
                                                     WHERE a.rowN=t1.rowN+1) IS NULL THEN GETDATE() ELSE
                                                    (SELECT MAX(dated)
                                                     FROM t1 AS a
                                                     WHERE a.rowN=t1.rowN+1) END AS date)
  AND RegNo=@regno RETURN @status;
end

I think this function might be causing problem, any suggesting in some direction will help

Update 1: After suggestion by @swasheck,@Lamak, @Tomtom,@AaronBertrand i have changed the query as folllows but it still keeps running for several minutes without any result, but now tempdb is not affecting, earlier for single student it was taking around 18 seconds which has been reduced to 4 second.

        SELECT ROW_NUMBER()over(partition BY b.regno
                                ORDER BY cast(CASE WHEN postdate IS NULL
                                              OR postdate='1900-01-01' THEN LedgerDate ELSE PostDate END AS DATE)) SRNo,
    CASE WHEN d.CrsCategoryDashBoard in('Admission cum scholarship test','ANTHE','MISC','LIBRARY','Transport Course') THEN b.LRTStatus
       ELSE CASE WHEN b.LRTStatus NOT in(0) THEN CASE WHEN h.LRTStatus IS NULL THEN b.LRTStatus  ELSE h.LRTStatus END ELSE b.LRTStatus END END AS LRTStatus ,
b.regno,'' AS studentName,c.InstAmt ,cast(round(c.InstAmt*e.TotalServiceTax/100,0)AS decimal(18,2)) serviceTax ,e.TotalServiceTax,                                                                     isnull(allottedConcession/ CASE WHEN e.TotalServiceTax=0 THEN 1 ELSE (1+(e.TotalServiceTax /100)) END,0) allottedConcession,isnull(allottedLumpsump/CASE WHEN e.TotalServiceTax=0 THEN 1 ELSE (1+(e.TotalServiceTax /100)) END,0) allottedLumpsump ,isnull(a.BaseAmount,0)Paidamount,isnull(a.ServiceTax,0)PaidServiceTax ,
                                                                                                                   isnull(a.Concession ,0)- (isnull(a.Concession ,0)/(1+((c.InstAmt*e.TotalServiceTax/100)/CASE WHEN BaseAmount=0 THEN 1 ELSE BaseAmount END)))PaidConcession,
        isnull(a.LumpSump,0) AS PaidLumpsump ,
        cast(CASE WHEN postdate IS NULL
             OR postdate='1900-01-01' THEN LedgerDate ELSE PostDate END AS DATE) AS RealisationDate,
        isnull(a.PayModeType,'') AS PaymodeType,
        isNull(a.PaymentStatus,'') AS PaymentStatus ,
        c.CourseName,
        CASE
            WHEN InstType IS NULL THEN CASE
                                           WHEN c.InstNo>1 THEN 'I'
                                           WHEN c.InstNo=-1 THEN 'A'
                                           WHEN c.InstNo=-2 THEN 'R'
                                       END
            ELSE InstType
        END AS Insttype,
        c.InstNo,
        d.CrsCategoryDashBoard,
        b.regdate,
        c.Instdt,
        c.startdt,
        cast(CASE WHEN d.CrsCategoryDashBoard IN ('Admission cum scholarship test','ANTHE','MISC','LIBRARY') THEN b.RegDate ELSE CASE WHEN D.CrsCategoryDashBoard='Transport Course' THEN CASE WHEN c.Instno=-2
             OR c.Instno=-1
             OR c.Instno=1 THEN b.RegDate ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END ELSE CASE WHEN c.Instno=-2 THEN b.RegDate WHEN c.Instno=-1
             OR c.Instno=1 THEN CASE WHEN RegDate>C.StartDt THEN RegDate ELSE c.StartDt END ELSE --In case student is transferred the regdate will be greater than installment date
        CASE WHEN RegDate>C.StartDt THEN CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END END END END AS date) AS InvoiceDate ,
        acct_type ,
        Acc_CenterCode
        FROM tbl_RegMaster AS b
        LEFT JOIN temp_CrsDetail AS c ON SUBSTRING(b.regno,1,3)+substring(b.regno,9,8)=c.crsCode
        LEFT JOIN Payment_Ledger_Matching AS a ON a.Regno=b.RegNo
        AND CASE
                WHEN a.InstType='R' THEN -2
                WHEN a.InstType='A'THEN -1
                WHEN a.InstType='I' THEN a.InstNo
            END= c.InstNo
        LEFT JOIN tbl_CrsCategory AS d ON c.CrsCategory=d.CrsCategory
        AND c.Acct_Type=d.Stream
        AND c.CrsCategoryYear=d.year
        INNER JOIN tbl_Stax AS e ON c.staxid=e.STaxID
        AND cast(CASE WHEN d.CrsCategoryDashBoard IN ('Admission cum scholarship test','ANTHE','MISC','LIBRARY') THEN b.RegDate ELSE CASE WHEN D.CrsCategoryDashBoard='Transport Course' THEN CASE WHEN c.Instno=-2
                 OR c.Instno=-1
                 OR c.Instno=1 THEN b.RegDate ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END ELSE CASE WHEN c.Instno=-2 THEN b.RegDate WHEN c.Instno=-1
                 OR c.Instno=1 THEN CASE WHEN RegDate>C.StartDt THEN RegDate ELSE c.StartDt END ELSE --In case student is transferred the regdate will be greater than installment date
        CASE WHEN RegDate>C.StartDt THEN CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END END END END AS date) BETWEEN e.FromDate AND e.ToDate --and IsCurrent=1
        LEFT JOIN temp_ConcessionDetails AS f ON b.regno=f.RegNo
        AND c.InstNo=f.InstNo
        JOIN tbl_CenterDetails AS g ON a.CenterId=g.CenterID
        Inner JOIN temp_StdStatusList AS h ON h.REGNO=B.RegNo
        AND cast(CASE WHEN d.CrsCategoryDashBoard IN ('Admission cum scholarship test','ANTHE','MISC','LIBRARY') THEN b.RegDate ELSE CASE WHEN D.CrsCategoryDashBoard='Transport Course' THEN CASE WHEN c.Instno=-2
                 OR c.Instno=-1
                 OR c.Instno=1 THEN b.RegDate ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END ELSE CASE WHEN c.Instno=-2 THEN b.RegDate WHEN c.Instno=-1
                 OR c.Instno=1 THEN CASE WHEN RegDate>C.StartDt THEN RegDate ELSE c.StartDt END ELSE --In case student is transferred the regdate will be greater than installment date
        CASE WHEN RegDate>C.StartDt THEN CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END ELSE CASE WHEN b.RegDate>Instdt THEN RegDate ELSE Instdt END END END END END AS date) BETWEEN h.Fromdate AND h.Todate
        WHERE substring(a.REGNO,1,3) IN (some center code they are around 60)
        AND SUBSTRING(c.crscode,5,8) IN (some course code which are around 200)
        AND c.CourseName NOT LIKE '%library%security%'
share|improve this question

closed as unclear what you're asking by Paul White, RolandoMySQLDBA, ypercube, Mark Storey-Smith, Phil May 17 at 8:46

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
is this the first time you've ever run this query? –  swasheck Nov 19 '13 at 19:14
4  
Who's waiting 8 hours for a query to finish? –  Aaron Bertrand Nov 19 '13 at 19:26
3  
TOP 100% ... ORDER BY is ignored by SQL Server. Since it knows that you're returning all rows, it no longer needs to use the ORDER BY to include/exclude rows. It is NOT used to maintain the order of output for any outer query - you should see this getting discarded in any plan. –  Aaron Bertrand Nov 19 '13 at 21:42
2  
@AmritpalSingh are you saying that the query is complete after 30 minutes and there's no result, or that at 30 minutes there's still no result? also, how do you know it's not consuming tempdb? did you shrink it? –  swasheck Nov 20 '13 at 15:27
1  
A general shotgun approach: Take all those functions and CASE statements in your JOINs and WHERE clauses, and load up #temp tables with exactly the columns you need, including the end results of the CASE statement or function (substrings, etc). Index those #temp tables. Then have your final query do JOINs and WHERE clause filtering solely on indexed columns directly - don't have a single function or CASE left in your JOINs and WHERE clauses. –  Anti-weakpasswords Apr 7 at 4:12

1 Answer 1

If it was working recently and it's not working now, you've either got a whole bucket load more data or a worse query plan. I'd suggest you'd notice the former. The latter may be due to the statistics getting stale.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.