Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

TempDB is growing out of control

I am running the following query and it is causing the tempdb to grow to over 100GB and run our of disk space. I'm stumped as to make it more effecient so it will not cause this behavior.

Thanks for any help! Here's the query:

      CREATE TABLE #ACMESites (site_id VARCHAR (120) COLLATE Latin1_General_BIN PRIMARY KEY)
     
      INSERT INTO #ACMESites
      SELECT site_id FROM ACMESites
            WHERE ACMESites.site_id IN (SELECT DISTINCT site_id FROM ACMEGroup_Members)

      UPDATE ACMEJournalPage SET SiteID = ACMERange.site_id
      FROM ACMEJournalPage
            INNER JOIN ACMEHostID ON ACMEJournalPage.HostID = ACMEHostID.HostID
            INNER JOIN ACMERange ON ACMEHostID.HostNum BETWEEN
                  ACMERange.low_address AND ACMERange.high_address
            INNER JOIN #ACMESites ON ACMERange.site_id = #ACMESites.site_id
            INNER JOIN ACMESite_Product ON ACMERange.site_id = ACMESite_Product.site_id AND
                  ACMESite_Product.product_id = ACMEJournalPage.Journal
0
charvett
Asked:
charvett
1 Solution
 
Scott PletcherSenior DBACommented:
--don't create a temp table

UPDATE ACMEJournalPage
SET SiteID = ACMERange.site_id
FROM ACMEJournalPage
            INNER JOIN ACMEHostID ON ACMEJournalPage.HostID = ACMEHostID.HostID
            INNER JOIN ACMERange ON ACMEHostID.HostNum BETWEEN
                  ACMERange.low_address AND ACMERange.high_address
            INNER JOIN (
                SELECT DISTINCT site_id
                FROM ACMEGroup_Members
            ) AS ACMEG_M ON ACMERange.site_id = ACMEG_M.site_id
            INNER JOIN ACMESite_Product ON ACMERange.site_id = ACMESite_Product.site_id AND
                  ACMESite_Product.product_id = ACMEJournalPage.Journal
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now