Veeam (VM tool) SQL Server Backup did not Shrink the Transaction Log

Today I received an interesting question from the VM admin of one of my consulting companies. The Question was, “I am backing up SQL Server Instances every day at 3am. I have my Veeam tab for SQL Server Logs set to “truncate the log”. Why is the transaction log staying the same size or growing for some of my databases but not all of them.”

Great Question!!

First of all shrinking a TLOG is not the same as truncation of a TLOG. Truncation of a TLOG is (not going into all the details) setting of the VLF (Virtual Log File) status to zero, if the VLF can be over written. Basically truncation of a TLOG, frees up space in the file.

Shrinking a TLOG is actually taking the file and making it smaller.

What the Veeam software was doing was truncation of the TLOG, not shrinking.

Unfortunately, the real problem was when the Veeam truncated the TLOG, the backup sequence was broken for the DBA.  So the only backup available was the Veeam 3am backup, which is fine if they do not need to restore point in time during the day.

The VM admin was also worried about the space the TLOGs were holding. The databases with the large transaction files were FULL Recovery Mode databases. The TLogs were not being backed up. So the TLogs file size will grow until it is backed up. For the databases that has SIMPLE Recovery Mode, the files were not growing.

In my next blog, I will go into detail about the SQL Server TLOG and how it works with backups, truncation, and shrinking. For now, take a look at these great blogs from others on the TLOG to understand more about why the TLOG in this case is growing.

Kalen Delaney: Geek City: Exploring the Transaction Log Structure

Erland Sommarskog: Large transaction log file (.ldf)

Polina Vasileva: Veeam Blog: How to back up a SQL Server transaction log

 

Brent Ozar – Brand Yourself

http://www.sqlpass.org/24hours/2016/summitpreview/Sessions/Details.aspx?sid=53732

Brent Ozar presentation slides from SQL PASS

I wish it was the recorded presentation, but it is just the slides. Brent is amazing on camera. Brent talked about how to take your career to the next level. He said he never graduated college and had problem finding a job because he did not have a degree. Taking your career to the next level means that jobs come to you instead of going out to find a job.

How many people know your work? Passive Networking – Write a good article or script and give it away for free. Other people will find it interesting and pass it on to their network.

Make your content easy to share. Don’t hide it behind a paywall or registration wall. Put easy-to-share buttons on your blog.

Resources for how:

• BrentOzar.com
• Brand yourself – Your brand is how you write not what you are writing about. Many bloggers write about the same topic, but each has a unique spin.

Books or blogs to read about marketing:

• TheWhuffieFactor – Tara Hunt
• DoubleYourFreelancing.com – Brennan Dunn
• The Referral Engine – John Jantsch
• TheLeanStartup.com – Eric Ries
• Purple Cow – Seth Godin

Brent’s favorite Blogs:

• Paul Randal: SQLskills.com
• Klaus Aschenbrenner: SQLPassion.at
• Kendra Little: LittleKendra.com
• Pinal Dave: SQLAuthority.com

Why is the Trans Log larger than the data inserted?

This was a fun question to figure out. At a SQL PASS meeting in Orange County this last week. One of the members asked all the DBAs, “When I use Select Into with Simple or Full recovery mode, why is the transaction log 4 times larger than the data inserted into the table?”

None of the DBAs could answer the question. So I spent after hours testing to find out.

This research paper includes the code to set up your own test. Great for learning more about the Transaction Log. See attached .pdf for the entire research paper: translog_selectinto_research

Transaction log size in each mode after the same “select into” statement was run. The log file was backed up and truncated between each test.

Type of Recovery Model	Log File size after transaction (MB)	Log file in use after Transaction (MB)	Max Log Row size after transaction	Number of log rows after transaction	Number of large rows after transaction
Simple	1023	132	11928	1225840	2
Full	4351	3278	12328	2048806	387090
Bulk-Logged	4351	222	12328	1327437	2

Summary:

The transaction log quadruples in size when running a “select into” with Full Recovery Model, because not only the data changes are recorded. Every memory change, lock, latch, split pages, etc. is recorded in the log. 

For the Simple model, the log file in use grew from zero to 132MB. Even though “Select Into” is minimally logged in the transaction log for Simple model, memory changes are tracked in the log. Items tracked in Simple model with a “Select Into” were page splits, memory allocation, latches, locks, and create table. Minimal logging does not mean no logging.