Loading ...
Sorry, an error occurred while loading the content.

Re: How to tune it & save space???????????

Expand Messages
  • vardhan_shashi
    HI, Try to use between clause in the select statement and try to insert 25% rows each time . Write the Select statement in transaction . For every 25% of
    Message 1 of 2 , Oct 12, 2007
    • 0 Attachment
      HI,

      Try to use between clause in the select statement and try to insert
      25% rows each time .
      Write the Select statement in transaction . For every 25% of insert
      Commit the trasaction ,this will free up the space in TEMPDB database.
      Try this ,if at all u face the same prob ,revert back , I can help u ………..


      Thank & Regards,
      Shashi





      --- In sqlcon@yahoogroups.com, mohd rizwan <mo2riz@...> wrote:
      >
      > Hi All,
      >
      > I have a query of bulk insert...
      >
      ------------------------------------------------------------------------------------------------------------------
      > SELECT IB_PROJECT_MAP.Region2, IB_SPARES_ITEMS.ITEM_NUMBER,
      IB_PROJECT_MAP.bucket_id,
      > SUM(IB_PROJECT_MAP.Installation *
      IB_SPARES_ITEMS.Quantity) AS Installation,
      > SUM(IB_PROJECT_MAP.Warranty *
      IB_SPARES_ITEMS.Quantity) AS Warranty,
      > SUM(IB_PROJECT_MAP.Other *
      IB_SPARES_ITEMS.Quantity) AS Other
      > INTO IB_MAPPED_SPARES_LOCAL_5years
      >
      > FROM IB_PROJECT_MAP INNER JOIN IB_SPARES_ITEMS
      >
      > ON IB_PROJECT_MAP.Consumption_End_Item =
      IB_SPARES_ITEMS.PROJECT_ITEM_NUMBER
      >
      > WHERE (IB_SPARES_ITEMS.SPAREABLE = 'Y')
      >
      > GROUP BY IB_PROJECT_MAP.Region2, IB_SPARES_ITEMS.ITEM_NUMBER,
      IB_PROJECT_MAP.bucket_id
      >
      ------------------------------------------------------------------------------------------------------------------
      >
      > This query is performing operations on 300 millions records that's
      what my tempdb is reaching to full disk size i.e.300+GB (free space)
      and it is getting fail with following error.
      >
      > Error: 1101, Severity: 17, State: 10
      > 2007-10-08 19:14:24.83 spid59 Could not allocate new page for
      database 'TEMPDB'. There are no more pages available in filegroup
      DEFAULT. Space can be created by dropping objects, adding additional
      files, or allowing file growth..
      >
      > I know..........it is space issue and I need to add more space or
      move tempdb on some other big drive but this time I am not having
      these options open.....so can anyone please tell me how to perform
      this task with only this much space................any trick to break
      this query in part so that after each part we can shrink tempdb and
      then can go ahead with next part...................or if any expert is
      having some good solution of this problem.....please share that would
      be a great help for me................
      >
      > A lot of thanks to all of you in advance............
      >
      >
      >
      >
      > Thanks & Regards,
      > Mohd Rizwan
      >
      > (¨`·.·´¨) Always
      > `·.¸(¨`·.·´¨) Keep
      > (¨`·.·´¨)¸.·´ Smiling!
      > `·.¸.·´
      >
      >
      > Send instant messages to your online friends
      http://uk.messenger.yahoo.com
      >
      > [Non-text portions of this message have been removed]
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.