How can I fix this error: Could not allocate space for object '(SYSTEM table id: -575370342)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full

Posted on 2007-10-11
Last Modified: 2010-08-05
I am getting so frustrated with this query as I needed this yesterday for a report.  

This is the most recent error I received.

Could not allocate space for object '(SYSTEM table id: -575370342)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full

Can someone tell me what it means and how to resolve it ASAP?  

This is the final draft of my query - ( got help fixing it from this board the past few days):

create table #promo (ctMarketingSKU varchar (50));

Insert into #promo  (ctMarketingSKU) values ('MXPAFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPAFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPALPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPAOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPASAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPELPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPESAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSLPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSSAREC');

            when p.swdatecreated is null then 'Not Activated'
            else 'Activated'
      end as status
      , #promo.ctMarketingSKU
      , p.swdatecreated as ActivationDate
      , c.ctorgid as ORGid
      , c.swname as Company
      , pe.swfirstname as ContactFirst
      , pe.swlastname as ContactLast
      , pe.swjobtitle as Title
      , pe.swextemailaddress
      , c.swmainphonearea as AreaCode
      , c.swmainphone as Phone#
      , a.swcity as City
      , a.swstate as State
      -- aditional fields
      , price.ctMarketingSKU
      , price.ctModel
      , price.ctSKUType
      , price.ctOpenPrice
      , price.ctChannelPrice
      , price.ctActive
      , price.ctUserCount
      , price.ctProductFamily
      , price.ctRevenueGroup
      , price.ctLicenseType
      , price.ctSubsAdvantage
      , price.swDateCreated
from sw_inst_product p (nolock)
     -- additional tables start
left join sw_prod_release r (nolock)
      on r.swprodreleaseid = p.swprodreleaseid
left join ct_Prod_release_skus sku (nolock)
      on sku.swprodreleaseid = r.swprodreleaseid
left join ct_sap_price price (nolock)
      on price.ctMarketingSKU = sku.ctMarketingSKU
left join #promo (nolock)
 on #promo.ctMarketingSKU = sku.ctMarketingSKU
-- additional tables end
inner join sw_customer c (nolock)
      on p.swcustomerid = c.swcustomerid
left join sw_address a (nolock)
      on c.swcustomerid = a.swobjectid
left join ct_inst_prod_subscription s (nolock)
      on p.swinstprodid = s.swinstprodid
left join sw_person pe (nolock)
      on s.swpersonid = pe.swpersonid
      a.swtype is null or a.swtype = 'Physical Location'
order by #promo.ctMarketingSKU

--drop table #promo

Question by:iquagmire
    LVL 75

    Accepted Solution

    the temp tables are created in tempdb and from the error message, i can see that your tempdb is full. there are two things you can do
    1. try to allocate more space for tempdb
    2. shrink the tempdb
    LVL 16

    Assisted Solution

    From your descriptions, I understood that your tempdb is growing unexpected
    and allocation errors with DBCC CHECKDB.

    First of all, have you upgraded your SQL Server to latest update?

    Then, you could perform stop SQL Server services, delete existing
    TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
    every time service is restarted.

    Run dbcc checkdb and dbcc newalloc on tempdb, let me know...

    Author Comment


    I am not a dba but someone that pulls reports.  Would I be able to delete teh tempdb.mdf and tebpdblog.ldf myself? if so, where would I do this?

    Also, where would I go to run dbcc checkdb and dbcc newalloc?  


    Author Comment

    Someone told me that adding more inner joins might help.  Would it?  If so, which fields should I try?

    Author Comment

    Any help??? Anyone? With the joins?
    LVL 16

    Expert Comment

    I think at your level it would inadvisable for you execute the task I sent earlier...You should have a dba handle this.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    if you can afford some server downtime, the easiest option will be restarting the sql server service / server, which indeed will recreate the tempdb.
    otherwise, as i suggested, you can use
    DBCC SHRINKFILE () statement

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now