?
Solved

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
7
Medium Priority
?
911 Views
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');


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

0
Comment
Question by:iquagmire
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 20058576
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
 
LVL 16

Assisted Solution

by:SQL_SERVER_DBA
SQL_SERVER_DBA earned 1000 total points
ID: 20058609
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
 

Author Comment

by:iquagmire
ID: 20059274
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:iquagmire
ID: 20059833
Someone told me that adding more inner joins might help.  Would it?  If so, which fields should I try?
0
 

Author Comment

by:iquagmire
ID: 20061091
Any help??? Anyone? With the joins?
0
 
LVL 16

Expert Comment

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

Expert Comment

by:Aneesh Retnakaran
ID: 20062635
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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