title: While pivoting 13 million records tempDB gets full in SQL Server and pivoting takes more than 28 hours
body: <p>I have one table which consumes 13 million records(data will increase in the future) which is approx 2.5gb Also, this table is not a Temporary table. When We try to pivot a table SQL give me the result as below </p>
  
  <blockquote>
    <p>Msg 1105, Level 17, State 2, Line 56<br>
    Could not allocate space for object 'dbo.WORKFILE GROUP large record overflow storage: 140761897762816' in database 'tempdb' because the 'PRIMARY' filegroup
    is full. Create disk space by deleting unneeded files, dropping
    objects in the filegroup, adding additional files to the filegroup, or
    setting autogrowth on for existing files in the filegroup</p>
  </blockquote>
  
  <p>Also, it takes too long to pivot. (more than 28 hours) there is no other task is running on that SQL server at that time.</p>
  
  <p>we have a total 16gb physical memory in the machine out of 16gb, 12gb allocated to SQL server. this machine has 4 core. there are total 4 filegroups for file type ROWS Data and one for LOG that all 5 files are located in the separate drive, the total size of that drive is 50gb. tempdb initial size is 14gb. Autogrowth for all datafile is 100MB and Maxsize are Unlimited.</p>
  
  <p>SQL query as below:</p>
  
  <pre><code>SET nocount ON 
  
  SELECT * 
  INTO isheetnewdata1_4_27 
  FROM 
      (SELECT 
           historyid, 
           requestentityid, 
           fieldname, 
           fieldvalue, 
           siteid, 
           isheetid 
       FROM   
           synk_isheet_1_int  
       WHERE  
           historyid = 6 
           AND group1id = 27 
           AND group2id = 4) AS A 
  PIVOT 
      (MAX(fieldvalue) 
       FOR fieldname IN ([ID], [LastName], [FirstName], [Age], [externalId])
      ) AS pvt 
  ORDER BY 
      historyid, requestentityid
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/2g8nw.png" alt="Table structure of synk_isheet_1_int "></p>
  
  <p>Please note that fieldname can be more which we have to pivot.</p>
  
  <p>I am using SQL Server 2016 in Microsoft Azure. My main concern is pivoting is taking more than 28 hours and due to that tempdb size is reached the maximum size of that drive.</p>
  
  <p>I have no idea what to do in this stage. should I have to increase the size of the drive where the tempdb database file is located? and increase physical memory of that machine and provide more memory to SQL Server?</p>
  
  <p><img src="https://i.stack.imgur.com/S60iO.png" alt="Query execution plan"></p>
  
  <p><img src="https://i.stack.imgur.com/aFf3o.png" alt="Expected pivot look like "></p>
  
  <p>Thanks all</p>