Inserting records failing everyday

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  November 8, 2016 at 9:06 am

  #318778

  We have one schedule job which dropping and inserting records into table on daily basis. But this job is failing due to "Could not allocate space for object 'dbo.SORT temporary run storage: 143199672664064' in database 'tempdb' because the 'PRIMARY' filegroup is full". I have added couple of tempdb with additional disk space but it's failing due to same reason.

  Table is inserting from view and it contains million of rows.

  select * into CONSUMPTION_SUPPLY

  from V_Consumption_Supply

  Is there any other way we write the query to eliminate the space issue?

  Thanks all for your help in advance.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 8, 2016 at 10:33 am

  #1911245

  EasyBoy (11/8/2016)

  ---

  We have one schedule job which dropping and inserting records into table on daily basis. But this job is failing due to "Could not allocate space for object 'dbo.SORT temporary run storage: 143199672664064' in database 'tempdb' because the 'PRIMARY' filegroup is full". I have added couple of tempdb with additional disk space but it's failing due to same reason.

  Table is inserting from view and it contains million of rows.

  select * into CONSUMPTION_SUPPLY

  from V_Consumption_Supply

  Is there any other way we write the query to eliminate the space issue?

  Thanks all for your help in advance.

  1) Definition of the view and all subordinate objects?

  2) Any triggers defined on the view or consumption_supply table?

  3) How much space in tempdb?

  4) Search online and find scripts to help you see what space usage is happening in tempdb. Glenn Berry's SQL Server Diagnostic Scripts may have some but not sure.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  November 11, 2016 at 12:50 pm

  #1911991

  EasyBoy (11/8/2016)

  ---

  We have one schedule job which dropping and inserting records into table on daily basis. But this job is failing due to "Could not allocate space for object 'dbo.SORT temporary run storage: 143199672664064' in database 'tempdb' because the 'PRIMARY' filegroup is full". I have added couple of tempdb with additional disk space but it's failing due to same reason.

  Table is inserting from view and it contains million of rows.

  select * into CONSUMPTION_SUPPLY

  from V_Consumption_Supply

  Is there any other way we write the query to eliminate the space issue?

  Thanks all for your help in advance.

  Check the view... If it has a DISTINCT or GROUP BY and a lot of joins, chances are there's an accidental cross-join (many-to-many) that's causing a bazillion internal rows to be generated and those frequently end up in TempDB as working tables.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)