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?

Why are you using temp tables why cant you directly insert into Main tables. since you know the columns to be inserted it shouldn't be a problem. — Pரதீப், Nov 07 '14 at 10:36
Thanks @Pradeep, we are looking on that part as well but right now let say that is needed. The problem we are facing is tempDB running out of space, so we are loking for a way to give the SQLServer time to work and clear. — GBrian, Nov 07 '14 at 11:06
What size is the disk that the temp db is on and is it full? Is temp db on a dedicated drive? Is it on a server or a local machine? If it's a server is this being done via RDP / Management Studio, or is it being run remotely from a local machine? — Tanner, Nov 07 '14 at 11:15
Can't you just increase the `TempDB` size to what is needed? — marc_s, Nov 07 '14 at 11:51
Thread related to your problem http://www.linkedin.com/groups/TempDB-is-acquiring-lots-space-66097.S.157140350 — knkarthick24, Nov 07 '14 at 11:55
Hi marc_s, is PROD environment so we are trying to solve by changing the procedure before going on this kind of solutions (for sure will solve inmediatly our current situation :)). I like Pred's answer pretty much about adding multiple files to tempDB. — GBrian, Nov 07 '14 at 12:26

score 1 · Answer 1 · edited May 23 '17 at 10:25

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:

Drop the 'temp' table if it exists
Create the 'temp' table (in a user database)
Fill the 'temp' table with the necessary data
Load the destination tables from the persisted 'temp' table
Drop the 'temp' table to free up space in data files
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?

score 0 · Answer 2 · answered Nov 08 '14 at 05:17

0

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.

answered Nov 08 '14 at 05:17

Bruce Dunwiddie

2,888
1
15
20

Hi, Currently does not implicit `BEGIN TRAN` so it is running `AUTOCOMMIT`. Do you mean extracting the code into an store procedure and calling it from a while loop will be enough ?, thanks – GBrian Nov 09 '14 at 07:50

sql server free tempdb

2 Answers2