Reducing the transaction log in SQL Server 1

[AndyInNC]

We are revising our data structure from 15 tables with 255 columns each to 1 (or possibly 5) tables with 6 columns each. Pivoting all this data -- or actually, UNpivoting -- is creating a lot of rows and therefore a lot of transactions. On a couple of occasions we filled up the transaction log.

Question:
In my conversion script, would using BEGIN TRANS / COMMIT around each of the INSERT / UPDATE statements have any effect on the Transaction Log?

Do you know where I could find this subject referenced?

Thanks!

 

[SQLBill]

Check out this:

http://www.red-gate.com/community/b...up&utm_content=tlog_ebook&utm_term=tlog_ebook

The pdf is free to download.

Now, to answer your question....depends...as I understand it, it will depend on your Recovery Mode. In Simple Recovery Mode: COMMITting the transactions will mark the log file section as 'reusable'. So SQL Server will be able to checkpoint it and overwrite it

However, in Full Recovery mode, it needs to stay in the transaction log until the next T-log backup.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Olaf Doschke]

The transaction log logs all actions on a database, not only those within a manual transaction.

What will reduce the usage of the transaction is doing transformations in temp tables or table variables.

A final step then will be writing the result into your target database and table.

Bye, Olaf.

 

[Olaf Doschke]

But you are still also right:

A manual transaction means less entries in the transaction log. If you produce 1000 rows of data and each row has 100 updates before it's complete that will mean just 1000 rows in the transaction log instead of 100,000, as without a manual transaction every single update causes a log of current and next record state. So yes, a manual transaction is a good idea.

Working with temp data can fill the tempdb log, which can also become a problem, working with a table variable and writing just the full result will work, too.

Bye, Olaf.

 

[AndyInNC]

SQLBill, your link to the book made me LOL that there would be an entire book dedicated to Transaction Logs.

Nevertheless, you are very much on the right track with the database Recovery Mode, which I had not considered.

In SQL 2008 R2, there are Simple, Full, and Bulk-Insert options. Generally, Full is recommended for a production environment. According to the downloadable PDF in your link (yes, I did reference it!), certain bulk-type operations can be minimally logged under the Bulk-Insert mode. Operations such as Select Into, Index creation, etc and the mode can be toggled to accommodate that change, but there could be a price if restoring is necessary during that time.

It seems that for Full mode, the BEGIN TRANS / COMMIT won't have the effect I was hoping for. That said, I'll do work-arounds like smaller data inserts and more backups in between or moving to another database or server to do my bulk work.

Thanks for the help!

 

[Olaf Doschke]

>It seems that for Full mode, the BEGIN TRANS / COMMIT won't have the effect I was hoping for.
That's certainly right. I can't tell exactly what you were hoping for.

But indeed a manual transaction on top of automatic ones reduces log writing.

In detail an insert write the new record in the log, any update writes old and new record, too. This all works on the granularity of pages rather than records, but that just makes it even worse.

If you wrap that in a transaction you only have one change for the insert and all updates you did on the same records/pages and only have one log entry including all the pages. So it helps lowering the amount of disc spaces used in the transaction log, indeed.

Bye, Olaf.