Dirty little SQL secrets – One night with DPM and my transaction log

Caveat -This is just my understanding, so it is probably fundamentally flawed, but this is how I understand things and hopefully I can impart some of that understanding to you of how DPM and SQL server spend their time where prying eyes cannot see.

I do not read the type of books that would allow me to convey the right mood for a story like this, for which I am very thankful, however, it diminishes the potential appeal of this story. Let’s face it, SQL Server is pretty dull and boring at the best of times; and System Center 2012 – Data Protection Manager, well.. yeah, I just know you are just bursting to read the next DPM White Paper, aren’t you De Jager.

Aim of this article

After a weekend of restlessness relating to SQL server transaction logs growing wildly and running out of disk space, effectively murdering our poor server I have endeavored to understand how DPM interacts with SQL. Understanding the different backup types that DPM uses to backup SQL server and how they affect SQL server transaction log files is crucial.

An explanation of DPM, short-term and long-term backups, disk and tape backups and all those exciting topics fall outside of the scope of this article.

Understanding DPM configuration

On our DPM servers we have various protection groups, many of which targets SQL servers. DPM then runs 2 distinct intervals. But this is not really intuitive. Let me explain with a little picture, see Image 1

Image 1

There are 2 schedules here, see the first one on top, this is the SQL schedule, which is repeated at the bottom. I have boxed them in pink. The second schedule is for the express full backup, this is a file state backup.

When you select the option to “Create recovery point” from the right click menu while navigating DPM, you will see the difference a bit more, see Image 2

Image 2

How does this affect transaction logs

We need to understand how DPM’s touch on the SQL server correlates to the size of your bonus or, conversely, how quickly it can shrink away.

  1. Let us understand backups.

There seems to be 2 distinct backup actions taking place with DPM when interacting with SQL server.

  1. Express full backup

The underlying SQL code looks something like this:

BACKUP DATABASE [DB] TO VIRTUAL_DEVICE=‘{GUID}’ WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024

What is a Snapshot? In short it is very much like a VM snapshot, and uses VSS to transfer a snap of the actual database files to DPM, targeting the Virtual Device in question. Setting SQL server backup compression does not affect this at all. For those of you not familiar with SQL server, it is best to think of this as a file system backup and not as a normal SQL backup.

  1. Incremental backup

Again, the clandestine SQL code looks something like the following:

BACKUP LOG [DB] TO DISK = N'<local path>\DB.log’

This looks a lot more SQLish, backing up a transaction log. This will also under normal circumstances truncate the log, which is the behavior that we are specifically looking for in this case.

  1. Let us understand SQL transaction log file size and why its size matters.

Transaction logs grow if you use your database. Inserts, updates, indexes, temporary tables and the list goes on, but in general you can expect a log to grow, as it keeps track of all the activities since the last full backup.

Normally DPM will run the BACKUP LOG command, which then triggers a backup to the default disk of the SQL log file. So if the default file location resides on D, all DPM log backups will write to that drive. After the backup, the log will truncate all data that precedes the last backup. So good backups will keep the growth in check and keep your log nice and tidy.

Now, if you imagine a case where the log file has had loads of activity and has grown to 200GB and there is only 180GB space available on the drive, then there will be a bit of a problem. At that moment, you will then have 180GB of growth space left before your database will start moaning and so will your users. Zero space left for the log to grow, thus each data altering transaction will fail.

It is vital that your log space usage never grows larger than your disk space available on the default log drive.

In order to make sure you never have an encounter like this, you need to know that running Full Backups on DPM will NEVER EVER help you if you want to truncate your SQL transaction logs, as Express Full backups are in essence VSS copies of the database files.

In Summary

When using DPM to do backups only 1 option can truncate transaction logs. That is doing an Incremental backup, with enough space left on the drive to backup the entire log.

You can also make your life a bit better by setting Backups to compress by default, if you have SQL Server 2008 R2 or higher flavors. Note however that there are CPU impact for compressing data, but you can give yourself a little extra buffer space for backups.

Or you can trust your backup administrator and not see your children over weekends.

More reading

Snapshot Backups

Backup compression and Virtual Device Interface (VDI)

Testing backup compression in SQL Server 2008

Why Some Backups Fail

Disclaimer

The Information Herein Is Provided “As Is” Without Warranty Of Any Kind, Either Expressed Or Implied, Including But Not Limited To The Implied Warranties Of Merchantability And/Or Fitness For A Particular Purpose. So Don’t Blame Me, I Can Do That Quite Properly By Myself, Thank You Very Much. If You Cannot Read, Or Find It Difficult, Then Get A Friend To Read It For You, If You Have No Friends, Ag Shame, Then You Need Help You Won’t Find Here Buddy.

All Characters Appearing In, Or Implied By, This Work Might Be Construed As Fictitious. Any Resemblance To Real Persons, Living Or Dead, Has Taken Some Of My Precious Time So Be Grateful. If You Did Not Notice Yourself, You A.) Might Be Too Thick To Notice Your Own Name, B.) You Cannot Read, C.) You Have No Friends Who Can Read, D.) Have No Friends, Ag Shame, E) Were Not Mentioned, F) Don’t Know How To Change Font Sizes, Poepol.

#bakups #dpm #sql #transactionlog

0 views0 comments

Recent Posts

See All

SQL transfer changes between 2 tables, mini-replication

I have 2 identical SQL servers, with identical I refer to schema and not data. What I need to do is get the same data in both. This is simple replication and would imply pulling the data from Table 1