What is Written to SQL Server Transaction Log?

disk-spacesql serversql server 2014transaction-log

Here's the scenario:

DB with Simple recovery model,
Roughly 14 GB of data (~80 million rows),
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,
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?

Best Answer

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.

Related Solutions

Effective Techniques to Reduce SQL Server Disk Space Use When Updating Large Table

I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

ALTER DATABASE [my_db] SET RECOVERY SIMPLE WITH NO_WAIT (check here for more info)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (check here for more info)
drop nonclustered indexes: this step will avoid the hassle with the need of updating them on each operation (check Brent Ozar blog for more details)
use batches: as suggested by mrdenny, do whatever you need to do, update, delete, inserts, in smaller chunks.

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO

restore nonclustered indexes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE [my_db] SET RECOVERY FULL;

SQL Server – Transaction Log Full Due to ‘XTP_CHECKPOINT’

I had a similar problem: I did not have replication but once I used Memory Optimized table as a test, database in Simple recovery mode, but my transaction logs weren't truncated. Manual truncation, even right after a full backup, gave the error:

Cannot shrink log file X because the logical log file located at the end of the file is in use.

A manual checkpoint failed:

Msg 41315, Level 16, State 4, Line N Checkpoint operation failed in database X.

A manual checkpoint only succeeded right after restarting the SQL Service, which would lead to a 4 hour In Recovery state because of my Multi Tb database size. I also tried to set the autogrowth to a specific size, but it all ended up doing the same: fill up the transaction logs until no space was left.

Finally, after days and nights trying and researching, I found the solution for my problem by installing the Cumulative Update 3 for SQL Server 2014 SP1

Best Answer

Related Solutions

Effective Techniques to Reduce SQL Server Disk Space Use When Updating Large Table

SQL Server – Transaction Log Full Due to ‘XTP_CHECKPOINT’

Related Question