Bulk Data Loading and Transaction Log

7 votes
2 answers
4790 views
sql-server sql-server-2008-r2 optimization bulk import
                          I'm currently working on a project which bulk import data from flat files (csv) about 18 different files each linking to a specific table through some stored procedure.

I followed the steps as  advised in Data Loading Performance guide .

The database is in BulkLogged recovery mode to minimize the logging, when executing the stored procedure below on a file containing 600000 rows I get an error

> Msg 9002, Level 17, State 4, Procedure SP_Import__DeclarationClearanceHistory_FromCSV, Line 34     
> The transaction log for database  is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

(for testing purposes I do a full backup before starting the import).

Looking at the log_reuse_wait_desc I see the following:

> log_reuse_wait_desc **CHECKPOINT**. All other import get imported successfully. 

Any input in solving this would be welcomed.

    PROCEDURE [dbo].[SP_Import_DeclarationClearanceHistory_FromCSV]
     	@FilePath [nvarchar](1000)
    AS
    BEGIN
        -- Creating a Temproary Table for importing the data from csv file.
        DBCC TRACEON(610)

        CREATE TABLE #DeclarationClearanceHistory
        (
              [ItemID] [int] IDENTITY(1, 1) NOT NULL ,
              [CMSDeclarationID] [bigint] NOT NULL ,
              [StatusCode] [nvarchar](10) NOT NULL ,
              [SubStatus] [nvarchar](10) NULL ,
              [DepartmentCode] [nvarchar](10) NULL ,
              [StartDate] [datetime] NULL ,
              [EndDate] [datetime] NULL ,
              PRIMARY KEY CLUSTERED ( [ItemID] ASC )
                WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                       ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
            )
        ON  [PRIMARY]
         
    	-- Inserting all the from csv to temproary table using BULK INSERT
        EXEC ('BULK INSERT #DeclarationClearanceHistory
        FROM ''' + @FilePath + '''
        WITH ( FIELDTERMINATOR = '''', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'', ORDER = ''ITEMID ASC'' );') ;
        
        -- By using MERGE statement, inserting the record if not present and updating if exist.
        MERGE dbo.DeclarationClearanceHistory AS TargetTable                            -- Inserting or Updating the table.
            USING #DeclarationClearanceHistory AS SourceTable                           -- Records from the temproary table (records from csv file).
            ON ( TargetTable.ItemID = SourceTable.ItemID )      -- Defining condition to decide which records are alredy present
            WHEN NOT MATCHED BY TARGET 
                THEN INSERT (
                              ItemID ,
                              CMSDeclarationID ,
                              StatusCode ,
                              SubStatus ,
                              DepartmentCode ,
                              StartDate ,
                              EndDate
                            )
                   VALUES   ( SourceTable.ItemID ,
                              SourceTable.CMSDeclarationID ,
                              SourceTable.StatusCode ,
                              SourceTable.SubStatus ,
                              SourceTable.DepartmentCode ,
                              SourceTable.StartDate ,
                              SourceTable.EndDate
                            )
            WHEN MATCHED -- If  matched then UPDATE
                THEN UPDATE
                   SET      TargetTable.ItemID = SourceTable.ItemID ,
                            TargetTable.CMSDeclarationID = SourceTable.CMSDeclarationID ,
                            TargetTable.StatusCode = SourceTable.StatusCode ,
                            TargetTable.SubStatus = SourceTable.SubStatus ,
                            TargetTable.DepartmentCode = SourceTable.DepartmentCode ,
                            TargetTable.StartDate = SourceTable.StartDate ,
                            TargetTable.EndDate = SourceTable.EndDate ;
    DBCC TRACEOFF(610)
    END
                        
Asked by Raymond (229 rep)
Dec 19, 2012, 01:58 PM
Last activity: Jan 20, 2022, 09:01 AM
Bulk Data Loading and Transaction Log

Related Questions
