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 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?

share|improve this question

migrated from stackoverflow.com Mar 7 '12 at 18:05

This question came from our site for professional and enthusiast programmers.

3  
So what HAS changed? Amount of data? What is your tempdb autogrow set to (perhaps it is capped - in which case free space on the disk is irrelevant)? –  Aaron Bertrand Mar 5 '12 at 21:12
    
@AaronBertrand i guess my question should be how do i shrink my tempdb? –  Yuck Mar 5 '12 at 21:13
3  
How is shrinking your tempdb going to help? From the error message it seems like tempdb is too small, not too big. –  Aaron Bertrand Mar 5 '12 at 21:16
    
not sure if related, but have you seen this other question? –  Paolo Falabella Mar 5 '12 at 21:22
    
Your query has a UNION ALL. Can you run each half, piping the results into a temp table, then query the result? That would just be a band-aid, of course, you may need to redesign your view. –  Jon of All Trades Mar 7 '12 at 23:14

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
im pretty sure tempdb is very limited on space –  Yuck Mar 5 '12 at 22:46
    
question is how to make it smaller –  Yuck Mar 6 '12 at 3:05
    
There is no magic switch for that. Optimize your query so that it does not require such enormous sorting space. (Looks like you are running on an underpowered server anyway.) –  usr Mar 6 '12 at 11:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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