4

Here's the scenario:

  1. DB with Simple recovery model,

  2. Roughly 14 GB of data (~80 million rows),

  3. INSERT ... SELECT statement into empty table with 7 non-clustered, non-unique, single column indexes and one clustered single column primary key from table with no indexes,

  4. Transaction logs set to have unlimited space with Autogrowth on by 2 GB

The query has been running for 3 hours now and transaction log files have grown to about 45 GB!

My question is simple - why? What is actually written to transaction log in this scenario? I read this article, this technet resource and this MSDN article but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

The table is not replicated. I finally decided to cancel my query and instead create keys/indexes/constraints on new table, drop the old one and rename it. It should be faster, right?

4

but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

No, in simple recovery mode, leaving Operations That Can be minimally Logged, all other operations are fully logged. In simple recovery model what is different is when the checkpoints are going to happen. Checkpoints are fired after transaction commits and when log file grows 70% of its size. For all other scenarios logging in simple recovery is same as full recovery.

Minimal Logging also requires Target Table to meet certain requirements

Roughly 14 GB of data (~80 million rows),

Were you performing insert into select * from type of query ? In that case you were doing lot of data reading and movement so transaction log file will record each page allocated to new table and depending on query. Now transaction log also keeps space for rollback of all the changes done. Keeping all this in mind I guess the space required would be genuine. I am not sure what query you ran and your SQL Server configuration, depending on that, answer can change.

There was a similar question I answered about what events are recorded in transaction log. There is NO documented command to read it but you can use undocumented command to read transaction log, I must tell you it requires level of expertise to interpret the output. Please read Analyzing Transaction Log By Remus Rusanu.

There are 3rd party tools available by RedGate and Apex which helps you in reading transaction log. You can search about them online.

  • Additionally, a process may have locked your source table for reading - which means your insert-select may have been waiting for a lock, and potentially also in deadlock. – youcantryreachingme Oct 16 '18 at 4:13
1

Everything is written to the transaction log, it is literally a log of every transaction that has taken place in your database,

To remove something from the transaction log the transaction must be:

  1. Complete
  2. Written to Disk
  3. If in Full recovery mode, backed up

If the transaction is not complete, then the transaction cannot be written to disk, and every transaction beyond that may be dependant on the results of this as data in memory may be changed earlier in the transaction which has now been read.

So any transactions that have ran since the start of a single long running query, cannot be backed up(in full mode) cannot be written to disk, and hereby cannot be removed from the transaction log to free space.

This also has the added effect of meaning that modified data that is stored in ram cannot be removed since it has not reached a checkpoint that indicated that that transaction has been committed to disk.

To work around this try batching your query, into smaller chunks to create multiple individual transactions rather than one large transaction

In regards to the size, the transaction log is quite literally every single transaction that modifies data, and as such in an update / insert heavy environment can generally fill a lot more than you'd expect. (eg incrementing a counter from 1 to 1000 by 1 each time is one row which is an int field in a table (4 bytes) in the transaction log it is 1000 entries dictating that this table this 'clustered index location' this column change to this value. it can quickly grow.

  • Suggest not using the term "transaction has been committed to disk" as it can be interpreted in a least a couple of different ways. Besides, transactions do not have to commit before data is written to disk. SQL Server will write dirty pages to the data file so long as the transaction log is up-to-date and can rollback if needed. – SQLmojoe Jun 1 '16 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.