When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log. This is implemented using Fast Load options in OLEDB Destination Editor.
Therefore, when designing Integration Services packages, consider the following:
- Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.
- Set a value for Maximum insert commit size. This option decides the size of data loading transaction. Specify the batch size that the OLE DB destination tries to commit during fast load operations. The default value of 214,748,3647 indicates that all data is committed in a single batch after all rows have been processed. With the default value the transaction log of database can grow up to the limit of disk size and can cause full transaction log error. The bigger problem may arise if you have implemented transaction and the task fails after a large amount of data load. In this case all data load will be rolled back and that may decrease the server performance significantly. So set the value of Maximum insert commit size to a moderate value like 10,000.
No comments:
Post a Comment