Timeout exception even after setting timeout property for operation

Question

Timeout issue:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\r\nThe the statement has been terminated.

I am having 17 millions of records to dump in my application database.These 12 million records are the result of comparison operation between 2 database records.

I compare 2 database records then populate mismatch records (based on some criteria) in the data table and once that data table reaches some limit like 1000 or 500 etc I send this data table to SQL bulk copy for bulk import and then empty the data table.

I am doing this whole operation inside the transaction so that I have inserted X records and during my comparison process any error comes so i will rollback those X records.

But because of this, I am getting a timeout issue doing then bulk copy.

I have checked varying different batchsize like 5000,1000,500,300 etc.I am getting timeout issues in all this batch size.

Once I have set bulk-copy timeout to 0 but then I go this below error :

The transaction log for my database is full.

With 1000 records it reaches 2.7 million and then throws timeout issues,

With 500 records it reached some 2.1 million records then throws an error.

With 300,200,100 also it is throwing timeout errors.

I also have set connection timeout in my connection string to 30 minutes.

Code :

public class SaveRepo : IDisposable
    {
        DataTable dataTable;
        SqlConnection connection;
        string connectionString;
        SqlTransaction transaction;
        SqlBulkCopy bulkCopy;
        int testId,

        public SaveRepo (int testId)//testId=10364
        {
            this.connectionString = connectionString;
            dataTable = new DataTable();
            connection = new SqlConnection(connectionString);
            connection.Open();
            transaction = connection.BeginTransaction();
            bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
            bulkCopy.BulkCopyTimeout = 60;
            bulkCopy.EnableStreaming = true;
            bulkCopy.DestinationTableName = "dbo.Sales";
            bulkCopy.BatchSize = 100;
            bulkCopy.SqlRowsCopied +=
                  new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
            bulkCopy.NotifyAfter = 100;
        }

       void Dump()
        {
            try
            {
                bulkCopy.WriteToServer(dataTable);
            }
            catch(Exception ex) // timeout error
            {
                throw ex;
            }
        }

    void FillDatatable(object[] row)
    {
        if (dataTable.Rows.Count == 100)
        {
           Dump();
           dataTable.Clear();
        }
        dataTable.Rows.Add(row);
    }

        public void End()
        {
            transaction.Commit();
            //dispose the stuffs also
        }
    }

Is there any other way or solution which I am missing and can solve this timeout issue?

Update : After setting BulkCopyTimeout to 0 and having batchsize =1000 i got this error till 3593000 records bulk copied:

Could not allocate space for object 'dbo.Sales'.'PK_dbo.Sales' in database 'XYZ' 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.

Update 2 : I removed the transaction and i will open and close connection for each of the batch and while dumping any batch if error occurs then i will removed all those previously saved data using testId.Now this works up to dumping 3 millions of data then i get this error :

Could not allocate space for object 'dbo.Sales'.'PK_dbo.Sales' in database 'XYZ' 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.

This goes in catch section where i try to remove old data based on testId but it takes so long and then it throws this error :

The transaction log for my database is full.

void Dump()
        {
            using (SqlConnection connection =
                  new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
                {
                    bulkCopy.DestinationTableName = "dbo.Sales";
                    bulkCopy.EnableStreaming = true;
                    try
                    {
                        bulkCopy.WriteToServer(dataTable);
                    }
                    catch(Exception ex)
                    {
                        connection.Close();
                        SalesRepo.Delete(connectionString, testId);
                    }
                }
            }
        }

Answer 1

Preface/NOTE: This is a solution that works good for some needs but may not/is not recommended for all situations and should be tested/evaluated if it is the best solution for what you are doing.

This is to solve the Transaction log issue filling up:

I had similar issue where I was working on something that was log file intensive and I filled it up a couple of times. The log file will shrink back down after the data is shipped/removed from the log file but this takes 3-8 minutes (depending on the DB and server settings). To alleviate this issue I created an SP that will check the log file and if it reaches a certain size it will WAIT for a given time period. All these values are variables you pass to the SP.

The way I used this was I put the SP call in my script and it would run and if the log file got too big it would WAIT giving the log file time to shrink back down before proceeding.

You call it by

EXEC dbo.LogFileFullCheckAndWaitFor 
     @DBNameToCheck = 'DBALocal', 
     @WaitForDealyToUse = '00:00:05.00', 
     @LogFileUsedPercentToCheck = '10'

@DBNameToCheck = The databases log file you want to check on

@WaitForDealyToUse = The time you want to WAIT before resuming your script (script uses WAITFOR DELAY). It must be in this format '00:00:05.00' (HH:MM:SS:MM), you can leave off the MM (milliseconds)

@LogFileUsedPercentToCheck = This is a number that is 2 decimal places that you will pass and if the log file exceeds this percentage it will trigger the WAIT. It will also instantly display a message in the SQL output window (without having to buffer anything). It does this by using RAISERROR, but NOTE it uses a low severity error number so it will not trigger an error for try/catch blocks (this was the only way I found to instantly display the message without the normal buffer time). This may not be needed if you are not executing in Management Studio.

Depending on your permission level this may/may not work.

USE [DBALocal]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[LogFileFullCheckAndWaitFor] (
    @DBNameToCheck VARCHAR(250),
    @WaitForDealyToUse VARCHAR(50),
    @LogFileUsedPercentToCheck DECIMAL(10,2)
)

AS
BEGIN

    SET NOCOUNT ON;


    BEGIN TRY
            -- table to hold the data returned from 
            DECLARE @LogSize AS TABLE (
                DatabaseName VARCHAR(250), 
                LogSize DECIMAL(10,2), 
                LogUsedPercent DECIMAL(10,2), 
                Status INT
            )

            DECLARE @LogUsedPercent AS DECIMAL(10,2)
            DECLARE @RaiseErrorMessage AS VARCHAR(1000)

            -- build out the error message here
            SET @RaiseErrorMessage = 'LOG FILE REACHED ' + CAST(@LogFileUsedPercentToCheck AS VARCHAR(50)) + ' full so pausing for ' + CAST(@WaitForDealyToUse AS VARCHAR(50)) + ' minutes'

            /*
                -- removed the below because may need higher permissions, so using query below below this instead

                INSERT INTO @LogSize
                EXEC('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;')    

                SELECT @LogUsedPercent = LogUsedPercent
                --select *,  CAST(LogSize*(LogUsedPercent * .01) AS DECIMAL(10,2)) AS TotalSizeUsed, CAST(LogSize - (LogSize*(LogUsedPercent * .01)) AS DECIMAL(10,2)) AS LogSizeLeft
                FROM @LogSize 
                WHERE DatabaseName = @DBNameToCheck 
            */

                --- this has lower required permissions then the above
                -- this gets the log file used percent
                SELECT @LogUsedPercent = cast(pc2.cntr_value*100.0/pc1.cntr_value as dec(5,2))
                FROM sys.dm_os_performance_counters (NOLOCK) AS pc1
                INNER JOIN sys.dm_os_performance_counters (NOLOCK) AS pc2 ON pc1.instance_name = pc2.instance_name
                WHERE  pc1.object_name LIKE '%Databases%'
                AND pc2.object_name LIKE '%Databases%'
                AND pc1.counter_name = 'Log File(s) Size (KB)'
                AND pc2.counter_name = 'Log File(s) Used Size (KB)'
                AND pc1.instance_name not in ('_Total', 'mssqlsystemresource')
                AND pc1.cntr_value > 0
                AND pc1.instance_name = @DBNameToCheck


            -- now if the current log file used percent is > what is passed, it displays a message, and waits for the time passed
            IF (@LogUsedPercent > @LogFileUsedPercentToCheck)
                BEGIN
                    SET @RaiseErrorMessage += ' Current Log Used Percent is: ' + CAST(@LogUsedPercent AS VARCHAR(50)) + ' '

                    -- Do this so it displays message immediatly, it is a low error message number so it will not be caught by the try catch blocks
                    -- but using the "WITH NOWAIT" displays the message instantly instead of waiting for  buffer to display
                    RAISERROR(@RaiseErrorMessage, 0, 1) WITH NOWAIT

                    -- now wait for the allowted time
                    WAITFOR DELAY @WaitForDealyToUse 
                END

            -- return the percent if they want to capture it
            SELECT @LogUsedPercent



    END TRY
    BEGIN CATCH

        -- run your catch logic here


    END CATCH
END