'MS SQL Database Skyrocketing from 2GB to 60+GB? [closed]

I have a database that starts about 2GB, which is about right for what I have in it.

I took 10k rows of a larger table and put it into a test table, and then ran it through my procedure. It runs separate processes for address, city, state, zip, and puts the results into a tmp file which range from 3-5MB. I ran each step of my procedure to figure out exactly where the issue is. Everything is fine creating the temp tables, it's joining them for the final output that is causing the issues. The process screeches to a halt and the database file works it's way up to 80GB (current max). The real problem here is that the full dataset is approx 170k, and it fills my drive (approx 140GB) and errors out and locks the computer.

Could not allocate a new page for database 'THRS' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I set Logging to 'Simple' and it used to get huge as well, but now doesn't grow much past 10MB or so. If I shrink the Files it goes down to 53.3GB, if I then Shrink the Database it drops back down to the 2GB.

It's not a complicated query so I don't know what in the world is going on.

SELECT
    CAST(0 AS BIT)                              AS [Verified],
    CAST(0 AS BIT)                              AS [Delete],
    CAST( ta.   [ID] AS varchar(10) )           AS [ID], 
    CAST( ta.   [Name] AS varchar(255) )        AS [Name],
    CAST( ta.   [Address1] AS varchar(255) )    AS [Address1], 
    CAST( tc.   [City] AS varchar(100) )        AS [City],
    CAST( ts.   [State] AS varchar(20) )        AS [State], 
    CAST( tz.   [Zip] AS varchar(20) )          AS [Zip],
    CAST( ta.   [cleanCity] AS varchar(100) )   AS [cleanCity], 
    CAST( tc.   [Confirmed] AS bit)             AS [City_Confirmed],
    CAST( ts.   [Confirmed] AS bit)             AS [State_Confirmed], 
    CAST( tz.   [Confirmed] AS bit)             AS [Zip_Confirmed]
  FROM [tmp_Address] ta  
  LEFT JOIN [tmp_City]  tc ON ( tc.[ID] = ta.[ID] ) 
  LEFT JOIN [tmp_State] ts ON ( ts.[ID] = ta.[ID] )  
  LEFT JOIN [tmp_Zip]   tz ON ( tz.[ID] = ta.[ID] )

Here is actual data from the tmp_tables

[tmp_Address]
|ID        |Name             |Address1         |cleanCity |
+----------+-----------------+-----------------+----------+
|a0t3s00000|Cale, Brad       |2500 52nd St     |Sioux City|
|a0t1I00000|Straight, Dustin |1704 G St        |Aurora    |
|a0t1I00000|Points, Eric     |4426 S 41st St   |Omaha     |
|a0t1I00000|Schroeder, Jordan|  2210 N 100th St|Omaha     |
|a0t1I00000|Nordahl, Carl    |11415 Pacific St |Omaha     |

[tmp_City]
|ID        |Original     |Orig_cleanCity|City         |Confirmed|
+----------+-------------+--------------+-------------+---------+
|a0t3s00000|OMAHA        |NULL          |OMAHA        | 0       |
|a0t3s00000|PLEASANT HILL|NULL          |PLEASANT HILL| 0       |
|a0t3s00000|TOPEKA       |NULL          |TOPEKA       | 0       |
|a0t3s00000|HAMLET       |NULL          |HAMLET       | 0       |
|a0t1I00000|SEDALIA      |NULL          |SEDALIA      | 0       |

[tmp_State]
|ID         |Original   |State  |Confirmed|
+-----------+-----------+-------+---------+
|a0t3s00000 |AK         |   AK  |1        |
|a0t3s00000 |AK         |   AK  |1        |
|a0t3s00000 |AK         |   AK  |1        |
|a0t3s00000 |AK         |   AK  |1        |
|a0t3s00000 |AK         |   AK  |1        |

[tmp_zip]
|ID        |Original    |Zip        |Confirmed|
+----------+------------+-----------+---------+
|a0t3s00000|00791       |00791      |1        |
|a0t3s00000|01075-2954  |01075-2954 |1        |
|a0t3s00000|01095       |01095      |1        |
|a0t3s00000|01524       |01524      |1        |
|a0t3s00000|01609       |01609      |1        |
|a0t3s00000|01749       |01749      |1        |
sqlsql-server


Solution 1:[1]

I set Logging to 'Simple' and it used to get huge as well, but now doesn't grow much past 10MB or so.

So the problem only happens when logging is in FULL recovery mode? In that's the case, don't forget you need to frequently backup the transaction log in addition to your regular full backups. It sounds like you are not running transaction log backups often enough.

"Wait", I can hear you say. "It's only a SELECT query. There are no transactions." Not true. This query is on a newly created "temp" table that might not even have an index (heap)... meaning there aren't good statistics and the query is likely to involve lots of writes (transactions) on tempdb.

You can probably also greatly improve performance by re-writing your temp table inserts as CTEs for this final SELECT. That is, if what you're really doing is building up data for this query, you can rewrite things to avoid actually inserting data into separate tables just to make these joins, and doing so will improve performance by several orders of magnitude.

Solution 2:[2]

Learning debugging skills and breaking down your problem will help you find the problem here, and in the future.

  1. Comment out or delete all the lines except the ones for your main table, and add a where clause to query for one id and see what that gets you:
SELECT
    CAST(0 AS BIT)                              AS [Verified],
    CAST(0 AS BIT)                              AS [Delete],
    CAST( ta.   [ID] AS varchar(10) )           AS [ID], 
    CAST( ta.   [Name] AS varchar(255) )        AS [Name],
    CAST( ta.   [Address1] AS varchar(255) )    AS [Address1], 
--    CAST( tc.   [City] AS varchar(100) )        AS [City],
--    CAST( ts.   [State] AS varchar(20) )        AS [State], 
--    CAST( tz.   [Zip] AS varchar(20) )          AS [Zip],
    CAST( ta.   [cleanCity] AS varchar(100) )   AS [cleanCity], 
--    CAST( tc.   [Confirmed] AS bit)             AS [City_Confirmed],
--    CAST( ts.   [Confirmed] AS bit)             AS [State_Confirmed], 
--    CAST( tz.   [Confirmed] AS bit)             AS [Zip_Confirmed]
  FROM [tmp_Address] ta  
--  LEFT JOIN [tmp_City]  tc ON ( tc.[ID] = ta.[ID] ) 
--  LEFT JOIN [tmp_State] ts ON ( ts.[ID] = ta.[ID] )  
--  LEFT JOIN [tmp_Zip]   tz ON ( tz.[ID] = ta.[ID] )
WHERE ta.[Id] = 'a0t3s00000';
  1. Now uncomment one additional table and its columns and see what you get:
SELECT
    CAST(0 AS BIT)                              AS [Verified],
    CAST(0 AS BIT)                              AS [Delete],
    CAST( ta.   [ID] AS varchar(10) )           AS [ID], 
    CAST( ta.   [Name] AS varchar(255) )        AS [Name],
    CAST( ta.   [Address1] AS varchar(255) )    AS [Address1], 
    CAST( tc.   [City] AS varchar(100) )        AS [City],
--    CAST( ts.   [State] AS varchar(20) )        AS [State], 
--    CAST( tz.   [Zip] AS varchar(20) )          AS [Zip],
    CAST( ta.   [cleanCity] AS varchar(100) )   AS [cleanCity], 
    CAST( tc.   [Confirmed] AS bit)             AS [City_Confirmed],
--    CAST( ts.   [Confirmed] AS bit)             AS [State_Confirmed], 
--    CAST( tz.   [Confirmed] AS bit)             AS [Zip_Confirmed]
  FROM [tmp_Address] ta  
  LEFT JOIN [tmp_City]  tc ON ( tc.[ID] = ta.[ID] ) 
--  LEFT JOIN [tmp_State] ts ON ( ts.[ID] = ta.[ID] )  
--  LEFT JOIN [tmp_Zip]   tz ON ( tz.[ID] = ta.[ID] )
WHERE ta.[Id] = 'a0t3s00000';

The problem should be apparent.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 jarlh

Related Questions