INF: Data Transformation Services and Logged Loads

If Data Transformation Services (DTS) loads from a flat file are taking a very long time, the load may be executing as a logged insert. If so, nonlogged inserts would be much faster. To determine whether a load is executing as logged or nonlogged, see "Determining Whether Load Is Executing as Logged or Nonlogged" in the MORE INFORMATION section of this article.

To ensure that your DTS package load is executing as nonlogged so that it runs faster, verify that all of the following are true for SQL Server versions 7.0 and 6.5:

SQL Server 7.0

• The table is not being published with either transactional or merge replication.
• The Select into/bulkcopy database option has been enabled.
• The Use fast load and Table lock options have been enabled on the Advanced tab of the Data Transformation Properties dialog box.

SQL Server 6.5

• The table is not being replicated.
• The table contains no data.
• There are no indexes defined on the table.
• The Select into/bulkcopy database option has been enabled.
• The Use fast load and Table lock options have been enabled on the Advanced tab of the Data Transformation Properties dialog box.

NOTE: If you set the Select into/bulkcopy option to true for a database to perform nonlogged data transfer, you must set it back to false and perform a differential or full database backup before you can perform subsequent transaction log backups. For more information, see "Creating and Applying Transaction Log Backups" in the SQL Server 7.0 Books Online.

Determining Whether Load Is Executing as Logged or Nonlogged

You can use SQL Server Profiler in SQL Server 7.0 or SQL Trace in SQL Server 6.5 to determine whether a given load is logged or nonlogged. A logged load will appear either as a series of INSERT statements (one for each row) or as a stored procedure call that wraps around an INSERT statement with a call to this stored procedure for each row. For example, if you are loading 1,000 rows, you should see either 1,000 INSERT statements or 1,000 EXEC calls to the stored procedure if the operation is logged.

In contrast, if the load is nonlogged, the individual row inserts will not appear in SQL Server Profiler or SQL Trace. You may see a few lines of activity captured as the utility prepared for the bulk insert, but you should not see an explicit INSERT or EXEC statement for each row loaded.

To capture the relevant information in SQL Server Profiler, you can use the "Sample 1 - TSQL" sample trace definition. To do this, perform the following steps:

1. Open SQL Server Profiler. On the File menu, point to Open and click Trace Definition.
2. In the Trace Name box, select Sample 1 - TSQL and click OK.

To see the relevant information in SQL Trace, you can create a new filter with the default selection criteria.

Another way to verify that a given load is non-logged is to check the table lock being used with sp_lock. In the case of a fast load, there will be only block update (BU) locks on the table.