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

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');


select
      case
            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
where
      a.swtype is null or a.swtype = 'Physical Location'
order by #promo.ctMarketingSKU

--drop table #promo

iquagmireAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
0
 
SQL_SERVER_DBAConnect With a Mentor Commented:
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...
0
 
iquagmireAuthor Commented:
SQL SERVER DBA,

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?  

Thanks!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
iquagmireAuthor Commented:
Someone told me that adding more inner joins might help.  Would it?  If so, which fields should I try?
0
 
iquagmireAuthor Commented:
Any help??? Anyone? With the joins?
0
 
SQL_SERVER_DBACommented:
I think at your level it would inadvisable for you execute the task I sent earlier...You should have a dba handle this.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.