CODEWITHSUNDEEP
sql-servertransactionssql-inserttransaction-log
Sanman Chavan
Sanman Chavan

Reputation: 61

SQL Large Insert Transaction log full Error

I am trying to insert almost 1,75,00,000 in 8 tables. I have stored procedure for that. At the start and end of that procedure, I have written Transaction.

Error: The transaction log for database 'Database' is full due to 'ACTIVE_TRANSACTION'.

Note: I want to keep everything in the transaction, its automated process. This process will run on Database every month

CREATE OR ALTER PROCEDURE [dbo].[InsertInMainTbls]
AS
BEGIN
PRINT('STARTED [InsertInMainTbls]')

DECLARE @NoRows INT
DECLARE @maxLoop INT
DECLARE @isSuccess BIT=1
BEGIN TRY

    BEGIN TRAN
        --1st table
        SET @NoRows = 1
        SELECT @maxLoop=(MAX([col1])/1000)+1 FROM ProcessTbl
        SELECT 'loop=='+ CAST(@maxLoop as Varchar)
        WHILE (@NoRows <= @maxLoop)
        BEGIN
                INSERT INTO MainTbl WITH(TABLOCK)
                ( col1,col2,col3....col40)
                SELECT
                ( val1,val2,val3....val40)FROM
                ProcessTbl
                WHERE [col1] BETWEEN (@NoRows*1000)-1000 
                AND (@NoRows*1000)-1
            SET @NoRows = @NoRows+1;
        END 
    --2nd table
    .
    .
    .
    --8th table
    SET @isSuccess=1;  
    COMMIT TRAN

END TRY
BEGIN CATCH

        PRINT ERROR_MESSAGE();
        SELECT  ERROR_MESSAGE() 'ErrorMsg'  ;
        SET @isSuccess=0;
    ROLLBACK TRAN

END CATCH

Upvotes: 0

Views: 866

Answers (1)

user7370003
user7370003

Reputation: 47

Despite the fact that is a nonsense to have such a huge transaction, while you can do a manual rollback by tagging the rows with something like a timesptamp or a GUID, to do so, you need to have the necessary space in the transaction log file to store all the rows of all your inserts from the first one til the last one, plus all the transaction that other user swill do at the same time. Many solutions to solve your problem : 1) enlarge your transaction log file 2) add some complementary log files 3) remove or decrease the transaction scope

Upvotes: 0

Related Questions