sql server - could not allocate space for object

Question

I am trying to retrieve the first 1000 records of my view and am getting this error message:

Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.SORT temporary run storage: 140751915712512' 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.

this is a new message. i have not seen it before. everything had been working.

here's my view:

SELECT     CONVERT(VARCHAR(8), a.[Date Entered], 1) AS [Full Date], DATEPART(yy, a.[Date Entered]) AS [Year Entered], LEFT(DATENAME(MONTH, a.[Date Entered]), 3) 
                      AS [Month Entered], DATEPART(dd, a.[Date Entered]) AS [Day Entered], CASE WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 1 THEN 'Sun' WHEN DATEPART(WEEKDAY, [DATE entered]) = 2 THEN 'Mon' WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 3 THEN 'Tus' WHEN DATEPART(WEEKDAY, [DATE entered]) = 4 THEN 'Wed' WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 5 THEN 'Thu' WHEN DATEPART(WEEKDAY, [DATE entered]) = 6 THEN 'Fri' WHEN DATEPART(WEEKDAY, [DATE entered]) = 7 THEN 'Sat' END AS DOW, 
                      CONVERT(VARCHAR(8), DATEADD(D, - (1 * DATEPART(dw, a.[Date Entered])) + 6, a.[Date Entered]), 1) AS [Week Ending], COUNT(DISTINCT a.[Specimen ID]) 
                      AS CountAccns, c.SALESREP AS [Sales Rep], c.NPI AS MLNPI, e.NPIb AS IMSNPI, e.SpecialtyPrimaryCodeb AS [IMS Specialty Primary Code], 
                      sm1.specialtydesc AS [Specialty Primary Description], e.SpecialtySecondaryCodeb AS [IMS Specialty Secondary Code], 
                      sm2.specialtydesc AS [Specialty Secondary Description], e.SpecialtyTertiaryCodeb AS [IMS Specialty Tertiary Code], 
                      sm3.specialtydesc AS [Specialty Tertiary Description], e.ProfessionalID1b AS [IMS Professional ID 1], a.[Requesting Physician] AS Physician, a.[Practice Code], 
                      b.[MLIS Practice ID] AS [MLIS Code], a.[Practice Name], c.DATEESTABLISHED AS [Date Established], c.PRACTICEADDRESS1 AS Address, 
                      c.PRACTICEADDRESS2 AS Address2, c.PRACTICECITY AS City, c.PRACTICESTATE AS State, d .[MLIS Status] AS Status, MAX(a.[Order Count]) AS [order count], 
                      CASE WHEN a.Outcome = 'NEGATIVE' THEN 'Released' WHEN a.Outcome = 'POSITIVE' THEN 'Released' WHEN a.outcome = 'Rejected' THEN 'Rejected' END AS [Release Status]
FROM         dbo.QuickLabDump AS a LEFT OUTER JOIN
                      dbo.qlmlismapping AS b ON b.[Quicklab ID] = a.[Practice Code] LEFT OUTER JOIN
                      dbo.PracticeandPhysician AS c ON a.[Requesting Physician] = c.DOCTORFIRSTNAME + ' ' + c.DOCTORLASTNAME AND 
                      a.[Practice Code] = c.PRACTICECODE LEFT OUTER JOIN
                      dbo.IMSData AS e ON c.NPI = e.NPIb LEFT OUTER JOIN
                      dbo.QLMLISInfo AS d ON b.[MLIS Practice ID] = d .[MLIS Practice ID] LEFT OUTER JOIN
                      dbo.SpecialtyMapping sm1 ON e.SpecialtyPrimaryCodeb = sm1.specialtyabbrev LEFT OUTER JOIN
                      dbo.SpecialtyMapping sm2 ON e.SpecialtySecondaryCodeb = sm2.specialtyabbrev LEFT OUTER JOIN
                      dbo.SpecialtyMapping sm3 ON e.SpecialtyTertiaryCodeb = sm3.specialtyabbrev
WHERE     (a.[Date Entered] > '20110101') AND (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          dbo.PendingSpecimens
                            WHERE      ([Specimen ID] = a.[Specimen ID])))
GROUP BY a.[Date Entered], c.SALESREP, c.NPI, e.NPIb, e.SpecialtyPrimaryCodeb, e.SpecialtySecondaryCodeb, e.SpecialtyTertiaryCodeb, e.ProfessionalID1b, 
                      a.[Requesting Physician], a.[Practice Code], b.[MLIS Practice ID], a.[Practice Name], c.DATEESTABLISHED, c.PRACTICEADDRESS1, c.PRACTICEADDRESS2, 
                      c.PRACTICECITY, c.PRACTICESTATE, d .[MLIS Status], sm1.specialtydesc, sm2.specialtydesc, sm3.specialtydesc, a.Outcome
UNION ALL
SELECT     CONVERT(VARCHAR(8), a.[Date Entered], 1) AS [Full Date], DATEPART(yy, a.[Date Entered]) AS [Year Entered], LEFT(DATENAME(MONTH, a.[Date Entered]), 3) 
                      AS [Month Entered], DATEPART(dd, a.[Date Entered]) AS [Day Entered], CASE WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 1 THEN 'Sun' WHEN DATEPART(WEEKDAY, [DATE entered]) = 2 THEN 'Mon' WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 3 THEN 'Tus' WHEN DATEPART(WEEKDAY, [DATE entered]) = 4 THEN 'Wed' WHEN DATEPART(WEEKDAY, [DATE entered]) 
                      = 5 THEN 'Thu' WHEN DATEPART(WEEKDAY, [DATE entered]) = 6 THEN 'Fri' WHEN DATEPART(WEEKDAY, [DATE entered]) = 7 THEN 'Sat' END AS DOW, 
                      CONVERT(VARCHAR(8), DATEADD(D, - (1 * DATEPART(dw, a.[Date Entered])) + 6, a.[Date Entered]), 1) AS [Week Ending], COUNT(DISTINCT a.[Specimen ID]) 
                      AS CountAccns, NULL AS [Sales Rep], NULL AS MLNPI, NULL AS IMSNPI, NULL AS [IMS Specialty Primary Code], NULL AS [Specialty Primary Description], NULL 
                      AS [IMS Specialty Secondary Code], NULL AS [Specialty Secondary Description], NULL AS [IMS Specialty Tertiary Code], NULL 
                      AS [Specialty Tertiary Description], NULL AS [IMS Professional ID 1], NULL AS Physician, SUBSTRING(agency, 1, charindex(' - ', agency, 1) - 1) AS [Practice Code], 
                      b.[MLIS Practice ID] AS [MLIS Code], SUBSTRING(agency, charindex(' - ', agency, 1) + 3, len(agency)) AS [Practice Name], c.DATEESTABLISHED AS [Date Established], 
                      c.PRACTICEADDRESS1 AS Address, c.PRACTICEADDRESS2 AS Address2, c.PRACTICECITY AS City, c.PRACTICESTATE AS State, d .[MLIS Status] AS Status, NULL 
                      AS [order count], 'Pending' AS [Release Status]
FROM         dbo.pendingspecimens AS a LEFT OUTER JOIN
                      dbo.qlmlismapping AS b ON b.[Quicklab ID] = SUBSTRING(agency, 1, charindex(' - ', agency, 1)) LEFT OUTER JOIN
                      dbo.PracticeandPhysician AS c ON SUBSTRING(agency, 1, charindex(' - ', agency, 1)) = c.PRACTICECODE LEFT OUTER JOIN
                      dbo.IMSData AS e ON c.NPI = e.NPIb LEFT OUTER JOIN
                      dbo.QLMLISInfo AS d ON b.[MLIS Practice ID] = d .[MLIS Practice ID]
WHERE     (a.[Date Entered] > '20110101')
GROUP BY agency, a.[Date Entered], c.SALESREP, c.NPI, e.NPIb, e.SpecialtyPrimaryCodeb, e.SpecialtySecondaryCodeb, e.SpecialtyTertiaryCodeb, e.ProfessionalID1b, 
                      b.[MLIS Practice ID], c.DATEESTABLISHED, c.PRACTICEADDRESS1, c.PRACTICEADDRESS2, c.PRACTICECITY, c.PRACTICESTATE, d .[MLIS Status]

again, it worked fine a few days ago.

there's a hard drive partition called sqltmpdb and it has 2 out of 10 gigs free.

question: how do i resolve this error message?

Answer 1

This error is appearing because your query requires SQL Server to sort a big amount of data.

Are you sure that there is enough disk space for all data files of temp db and that the data files have no size restriction (SQL Server has an option to restrict a file's size)? These are the only problems I can imagine.