Could not allocate space for object '<temporary system object: 422212632707072>' in database 'tempdb' because the 'PRIMARY' filegroup is full

Question

I am trying to execute this statement but I got this error:

Could not allocate space for object '' 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.

SQL statement:

DECLARE
@curvaTipicaPeriodoMes VARCHAR(50),
@curvaTipicaPeriodoAnio VARCHAR(50),
@curvaTipicaTipo VARCHAR(50),
@curvaTipicaTipoInicial VARCHAR(50),
@fronteraEstado VARCHAR(50),
@fronteraFechaFin DATETIME,
@birrelacion VARCHAR(50),
@birrelacionTipo VARCHAR(50)

SET @curvaTipicaPeriodoMes='10'
SET @curvaTipicaPeriodoAnio= '2014'
SET @curvaTipicaTipo='ValorTipico'
SET @curvaTipicaTipoInicial='ValorTipicoInicial'
SET @fronteraEstado='Operacion'
SET @fronteraFechaFin = NULL
SET @birrelacion='Bir0111'
SET @birrelacionTipo='Tfr0005'

SELECT
    Consulta.tipoDia,
    CASE WHEN Consulta.ValorTipico > 0 THEN Consulta.ValorTipico ELSE Consulta.ValorTipicoInicial END AS Calculo
FROM
   (SELECT 
        C.tipoDia,
        (SELECT (ISNULL(SUM(CC.periodo01),0) + ISNULL(SUM(CC.periodo02),0) + ISNULL(SUM(CC.periodo03),0) + ISNULL(SUM(CC.periodo04),0) + ISNULL(SUM(CC.periodo05),0) + ISNULL(SUM(CC.periodo06),0) + ISNULL(SUM(CC.periodo07),0) + ISNULL(SUM(CC.periodo08),0) + ISNULL(SUM(CC.periodo09),0) +  ISNULL(SUM(CC.periodo10),0) + ISNULL(SUM(CC.periodo11),0) + ISNULL(SUM(CC.periodo12),0) + ISNULL(SUM(CC.periodo13),0) + ISNULL(SUM(CC.periodo14),0) + ISNULL(SUM(CC.periodo15),0) + ISNULL(SUM(CC.periodo16),0) + ISNULL(SUM(CC.periodo17),0) + ISNULL(SUM(CC.periodo18),0) + ISNULL(SUM(CC.periodo19),0) + ISNULL(SUM(CC.periodo20),0) + ISNULL(SUM(CC.periodo21),0) + ISNULL(SUM(CC.periodo22),0) + ISNULL(SUM(CC.periodo23),0) + ISNULL(SUM(CC.periodo24),0)
)
FROM 
[registrar].[CurvaTipica] CC 
WHERE
CC.tipo = @curvaTipicaTipo 
AND CC.tipoDia=C.tipoDia
AND
DATEPART(MONTH,CC.periodo) = @curvaTipicaPeriodoMes 
AND DATEPART(YEAR,CC.periodo) = @curvaTipicaPeriodoAnio 
) * COUNT(C.tipoDia)
AS ValorTipico,
(
SELECT 
(
ISNULL(SUM(CC.periodo01),0) + ISNULL(SUM(CC.periodo02),0) + ISNULL(SUM(CC.periodo03),0) + ISNULL(SUM(CC.periodo04),0) + 
ISNULL(SUM(CC.periodo05),0) + ISNULL(SUM(CC.periodo06),0) + ISNULL(SUM(CC.periodo07),0) + ISNULL(SUM(CC.periodo08),0) +
ISNULL(SUM(CC.periodo09),0) + ISNULL(SUM(CC.periodo10),0) + ISNULL(SUM(CC.periodo11),0) + ISNULL(SUM(CC.periodo12),0) + 
ISNULL(SUM(CC.periodo13),0) + ISNULL(SUM(CC.periodo14),0) + ISNULL(SUM(CC.periodo15),0) + ISNULL(SUM(CC.periodo16),0) +
ISNULL(SUM(CC.periodo17),0) + ISNULL(SUM(CC.periodo18),0) + ISNULL(SUM(CC.periodo19),0) + ISNULL(SUM(CC.periodo20),0) + 
ISNULL(SUM(CC.periodo21),0) + ISNULL(SUM(CC.periodo22),0) + ISNULL(SUM(CC.periodo23),0) + ISNULL(SUM(CC.periodo24),0)
)
FROM 
[registrar].[CurvaTipica] CC 
WHERE
CC.tipo = @curvaTipicaTipoInicial
AND CC.tipoDia=C.tipoDia
AND
DATEPART(MONTH,CC.periodo) = @curvaTipicaPeriodoMes 
AND DATEPART(YEAR,CC.periodo) = @curvaTipicaPeriodoAnio 
) * COUNT(C.tipoDia)
AS ValorTipicoInicial
FROM [registrar].[CurvaTipica] C 
INNER JOIN [dbo].[Frontera] F ON c.frtID=F.objID 
INNER JOIN [dbo].[Birrelacion] B ON B.objID1=C.frtID
WHERE
DATEPART(MONTH,C.periodo) = @curvaTipicaPeriodoMes 
AND
DATEPART(YEAR,C.periodo) = @curvaTipicaPeriodoAnio 
AND
F.estado = @fronteraEstado 
AND
F.fechaFin IS NULL 
AND
B.objID = @birrelacion 
AND
B.objID2 = @birrelacionTipo
GROUP BY 
C.tipoDia
) Consulta
ORDER BY
Calculo

I checked and the tempdb is at 1400mb size and autogrowth at 10%.

Answer 1

I am having a really hard time understanding the context of this query which prevents me from providing what I think is the full potential of an answer here. In particular, I am baffled as to why your sums multiply the count of tipoDia when you are grouping by tipoDia.

In short, there are a few things going on that I see can cause large and undesirable log growth:

• The way you are handling the date restrictions: You are basically needing to run that datepart on the entire table before you can filter. Change this to a BETWEEN with no functions on the column in the table.
• The very unusual nesting: I just can't make sense of it but I suspect this is causing enormous temp space usage. I put some sample code below of how you might get away from this, but I realize it is not complete to match your original statement. It should be able to point you in the right direction. If nothing else, break out your query into three or four smaller temp tables then join those back together rather than trying to have everything in one massive statement with that many joins.

• The NULLIFs: This is really again related to the nesting, but I suspect that the NULLIF is running on every column you have listed for every row of the table. Get rid of the nested statements to fix this as well or do your additions outside of the nested section.

  DECLARE
      @curvaTipicaPeriodoBegin datetime,
      @curvaTipicaPeriodoEnd datetime,
      @curvaTipicaTipo VARCHAR(50),
      @curvaTipicaTipoInicial VARCHAR(50),
      @fronteraEstado VARCHAR(50),
      @birrelacion VARCHAR(50),
      @birrelacionTipo VARCHAR(50);
  
  SET     @curvaTipicaPeriodoBegin = '2014-10-01';
  SET     @curvaTipicaPeriodoEnd = '2014-10-31';
  SET     @curvaTipicaTipo='ValorTipico';
  SET     @curvaTipicaTipoInicial='ValorTipicoInicial';
  SET     @fronteraEstado='Operacion';
  SET     @birrelacion='Bir0111';
  SET     @birrelacionTipo='Tfr0005';
  
  SELECT
      CurvaTipica.tipoDia,
      CurvaTipica.tipo,
      SUM
          (
          ISNULL(CurvaTipica.periodo01,0) + ISNULL(CurvaTipica.periodo02,0) + ISNULL(CurvaTipica.periodo03,0) + ISNULL(CurvaTipica.periodo04,0) + 
          ISNULL(CurvaTipica.periodo05,0) + ISNULL(CurvaTipica.periodo06,0) + ISNULL(CurvaTipica.periodo07,0) + ISNULL(CurvaTipica.periodo08,0) +
          ISNULL(CurvaTipica.periodo09,0) + ISNULL(CurvaTipica.periodo10,0) + ISNULL(CurvaTipica.periodo11,0) + ISNULL(CurvaTipica.periodo12,0) + 
          ISNULL(CurvaTipica.periodo13,0) + ISNULL(CurvaTipica.periodo14,0) + ISNULL(CurvaTipica.periodo15,0) + ISNULL(CurvaTipica.periodo16,0) +
          ISNULL(CurvaTipica.periodo17,0) + ISNULL(CurvaTipica.periodo18,0) + ISNULL(CurvaTipica.periodo19,0) + ISNULL(CurvaTipica.periodo20,0) + 
          ISNULL(CurvaTipica.periodo21,0) + ISNULL(CurvaTipica.periodo22,0) + ISNULL(CurvaTipica.periodo23,0) + ISNULL(CurvaTipica.periodo24,0)
          ) ValorTipico
  FROM
      registrar.CurvaTipica
      JOIN 
      Frontera
      ON
          CurvaTipica.frtID = Frontera.objID
      JOIN
      Birrelacion
      ON
          CurvaTipica.frtID = Birrelacion.objID1
  WHERE
      CurvaTipica.periodo BETWEEN @curvaTipicaPeriodoBegin AND @curvaTipicaPeriodoEnd
      AND
      Frontera.estado = @fronteraEstado 
      AND
      Frontera.fechaFin IS NULL 
      AND
      Birrelacion.objID = @birrelacion
      AND
      Birrelacion.objID2 = @birrelacionTipo
      AND
      (
      CurvaTipica.tipo = @curvaTipicaTipo
      OR
      CurvaTipica.tipo = @curvaTipicaTipoInicial
      )
  GROUP BY
      CurvaTipica.tipoDia,
      CurvaTipica.tipo;