sql server free tempdb

Question

We are running an import stored procedure in SQL Server 2008R2-SP1 that loads thousand of rows into several tables at a time. We are having problems with tempDB and transaction log size.

Something like this:

CREATE PROCEDURE spReallyHugeImportDataProcedure
    @id int
AS
BEGIN
    CREATE TABLE #temp(...)

    INSERT INTO #temp
    SELECT *
    FROM AlotOfJoins

    INSERT INTO FinalTable
    SELECT * FROM AlotOfJoins

    DROP #tempTable

    INSERT INTO #temp
    SELECT *
    FROM AlotOfJoins

    INSERT INTO FinalTable
    SELECT * FROM AlotOfJoins

    DROP #tempTable

    INSERT INTO #temp
    SELECT *
    FROM AlotOfJoins

    INSERT INTO FinalTable
    SELECT * FROM AlotOfJoins

    DROP #tempTable

    -- And so on....

END

We are trying to split the whole process and run several times for a small set of data.

Like this:

CREATE PROCEDURE spReallyHugeImportDataProcedure
   @id int
AS
BEGIN

    DECLARE @SomeSortOfCounter int = 100
    WHILE(@SomeSortOfCounter <> 0)
    BEGIN
        -- TRY TO START A NEW TRANSACTION
        BEGIN TRAN

        CREATE TABLE #temp(...)

        INSERT INTO #temp
        SELECT *
        FROM AlotOfJoins
        WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter

        INSERT INTO FinalTable
        SELECT * FROM AlotOfJoins

        DROP #tempTable

        INSERT INTO #temp
        SELECT *
        FROM AlotOfJoins
        WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter

        INSERT INTO FinalTable
        SELECT * FROM AlotOfJoins

        DROP #tempTable

        INSERT INTO #temp
        SELECT *
        FROM AlotOfJoins
        WHERE SomeFileterWorkinWithTheCounter = @SomeSortOfCounter

        INSERT INTO FinalTable
        SELECT * FROM AlotOfJoins

        DROP #tempTable

        -- And so on....

        -- TRY TO RELASE TEMP OBJECTS, 
        -- OR GIVE TO THE SERVER THE OPORTUNITY TO DO IT
        COMMIT

        SET @SomeSortOfCounter = @SomeSortOfCounter - 1
    END
END

Is it possible for the SQL Server engine to work between those internal transactions?

Answer 1

simple answer is yes, as long as there is no outer transaction outside of the stored proc. also, there's no reason to add in explicit transactions. simply creating a loop and only working with a chunk of records per statement will let your tlog space get reused and you won't be forcing all of the records at one time into a temp table.

Answer 2

Option 1: Using a table in a user database

If you really need to store the data in a temporary table, build that table in a user database (ImportTemp for example or in your destination DB) then use it instead of the tempdb. In this case, the SQL Server should not use as much space in TempDB and your data will be stored persistently -> you can reuse it and you can split your loader queries into multiple batches.

Optionally you can move this table to a different filegroup to prevent concurent writing and lower the chance of interferring with other processes.

In this case, the steps are:

1. Drop the 'temp' table if it exists
2. Create the 'temp' table (in a user database)
3. Fill the 'temp' table with the necessary data
4. Load the destination tables from the persisted 'temp' table
5. Drop the 'temp' table to free up space in data files
6. Optionally shrink the data and log files which are related to your 'temp' table

If you are using this import table relatively frequently, you can only truncate it before and after use instead of dropping and recreating it.

Option 2: Using an ETL tool

Use an ETL tool which can handle data in batches / buffers. If you are using SQL Server Standard or above you have the option to use SSIS (SQL Server Integration Services).

DBCC SHRINKFILE

You can release unused space from data and log files using the DBCC SHRINKFILE command:

USE [YourDatabase]

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

Example

USE [tempdb]
DBCC SHRINKFILE (tempdb_data, TRUNCATEONLY)

Optionally

You can spread the TempDB files accross drives by adding additional data files to the TempDB:

ALTER DATABASE tempdb
    ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);

A related question: How to spread tempdb over multiple files?