Sql-server – Is it good idea to repair sql file

sql servertransaction-log

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?

Best Answer

Logging occurs in all recovery models. The log is retained in bulk logged and full recovery models until the log is backed up. Bulk logged only minimally logs certain actions e.g. an index rebuild, meaning you can't do point in time restores when using bulk logged. As you are using simple recovery model, changing to bulk logged will be of no benefit to reducing log size. It would make your setup more complex actually as you would have to start taking regular log backups.

If you have a large transaction in simple recovery model, this transaction will still be logged and cause your log file to grow. This is what allows you to rollback the transaction in any recovery model. If you left that transaction open (don't commit or rollback) your log would grow and grow. Once committed (or rolled back) that logging information no longer has to be retained in simple recovery model and SQL server will reuse the log in a cyclic nature.

Your solution would be to minimise the size of your ETL insert transactions. You could perform smaller inserts in batches and commit those (if that is appropriate for data consistency). You could also look at transaction handling in your ETL tool. SSIS for example allows all inserts in a package to be part of the same transaction, you could change this behaviour so each transformation has it's own transaction.

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table. If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling. Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

Make sure your clustered index keys match when possible. This means there is no on-the-fly sorting
Disable all non-clustered indexes. Self-explanatory.
Set recovery mode to simple and trace flag 610 to ON. Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards. Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.

Sql-server – Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system

If you log grows on ETL in simple mode it means your inserts are not minimally logged. The requirements for minimal logging are described in Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import:

Minimal logging requires that the target [of a INSERT BULK operation] table meets the following conditions:

The table is not being replicated.

Table locking is specified (using TABLOCK).

The Data Loading Performance Guide also mentions the TF 610 effects and how it affects minimal logging.

Best Answer

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

Sql-server – Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system

Related Question