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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Someone told me that adding more inner joins might help. Would it? If so, which fields should I try?
ASKER
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
otherwise, as i suggested, you can use
DBCC SHRINKFILE () statement
ASKER
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!