Link to home
Start Free TrialLog in
Avatar of iquagmire
iquagmire

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of iquagmire
iquagmire

ASKER

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