Page 1 of 1

SQL Server Backups & Files:How NOT to get fired in an ugly way!

#1 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 571
  • View blog
  • Posts: 1,272
  • Joined: 24-April 12

Posted 24 August 2013 - 09:01 AM

The whole issue of files, file space, and backups in SQL Server is more than a little confusing. Most explanations of it are incomplete and full of technical jargon. But really, once you figure out what's going on with it, it’s reasonably straight forward.

Getting Fired
I joke in the title of "How NOT to get fired in a very ugly way!" but it's only half joking. I suppose the way my career as a DBA started left a very strong impression on me as to the importance of backups. To make a long story short, I was working as a database programmer for a Fortune 500 company and the DBA in charge of the database somehow failed to do proper backups. The server crashed, and there were no backups of the company's critical data. Needless to say, that was the DBA's last day with the company. I never saw him again. And that was the beginning of me getting on the database support team, and eventually becoming a DBA. So in my mind, backups have always been the absolute first priority above all else. I like to say, "Make absolutely certain you have good backups, most other mistakes you could make as a DBA are forgivable if you have good backups." And knowing how they work is critical to setting them up properly and having good backups when the time comes where you need them. So, let's take a look at what's going on with the files that make up a SQL Server database and how to properly backup the information within them.

SQL Server Files

In SQL Server you have 3 types of files that store all the information for your databases. You have the “.MDF”, “.NDF”, and “.LDF” files. The “.LDF” files are your Transaction Logs; there is one Transaction Log for each database including your databases and the system databases that store information for the whole server. The “.MDF” files are your databases and the system databases. You may have “.NDF” files - which are optional; they are additional files for your database. People rarely use “.NDF” files, and when they do, it is usually to partition their data onto multiple hard-drives.

Part of the confusion that most people have is that these files have an internal size as well as an external size. So, with any of these files, you have to deal with the amount of free space on the hard-drive, the file’s external size, and it’s internal size. The external size is essentially what is set using “Initial Size”. This is space that SQL Server pre-allocates on the hard-drive; that is, it creates a file – an “.MDF” for example – and fills it up with empty space. So, the file takes that size immediately when it is created, which is why it’s called the initial size. At that point, the database and the file that contains it can be almost completely empty, but the file can be taking up the whole hard-drive … if you set the initial size to take up the whole hard drive. I call that the file’s “external” size, and you can confirm it by locating the “.MDF”, “.NDF”, or “.LDF” file on the hard-drive and looking at the file’s size in Explorer.

But when you first create them, the files may all be almost empty. Eventually, as you use the databases - and put more information into them – their files fill up with data. I call this the file’s “internal” size. It’s more difficult to see this happening, and it’s usually where people get confused. But you can see what I am talking about in MS SQL Server Management Studio. If you right-click on the database name and select Reports=>Standard Reports=>Disk Usage, you get a report that shows essentially what I’m talking about (This does not work if the database is set to compatibility level 80 [i.e. it’s a 2000 database]). The pie-charts that it shows assume that 100% is the complete file, not the size of the hard-drive, etc. So, Data Files Space Usage:Unallocated show’s how much space is empty inside the “.MDF” file (Actually, I believe it’s combining information of all “.MDF” and “.NDF” files into one chart.) The Transaction Log Space Usage:Unused shows the amount of space that is still empty inside the “.LDF” file. Probably the biggest drawback of this report is that it only shows you how things are at the moment the report is taken; to make this information really useful you need to track it throughout the day every day.

So, as you put more information into the database the “.MDF” file will fill up internally until it’s 100% full. When it reaches 100% full, one of two things will happen: If the database is set with Autogrow off, the database will crash. If the database is set with Autogrow on, the database will expand the file’s external size until it fills up the hard-drive and THEN it will crash. Either way the database crashes because it’s out of space. I recommend setting Autogrow off after you get a good idea of how much space you actually need (by monitoring the internal size for a month or two). Most people set Autogrow on, but then get lazy and don’t monitor the internal size, and so it just Autogrows until it crashes anyway. The downside of Autogrow is it can cause the files to become fragmented on the hard-drive. You could shutdown the database and defragment the hard-drive, but people rarely do this. As the files become fragmented it will slow down the database. That’s why I recommend just allocating as much space on the drive as you are going to likely need in the next year, and then just monitoring the internal size on a daily basis through SQL Server Jobs written with T-SQL Scripts (Personally, I like to record the info in a database table every five minutes and then use MS Excel to import the data and graph it so that it’s easy to analyze).

The Transaction Log

Now, the “.LDF” files are probably the biggest source of confusion and problems. The “.LDF” file is the database’s Transaction Log. The Transaction Log is used for several purposes. First, it’s definitely not an “Error Log”, which is where a lot of people get confused. Secondly, it’s main purpose is to keep track of everything that happened in the database. Third, it is used in Transactional Replication, which is another source of confusion.

Primarily, the Transaction Log records “Transactions”. Almost everything that happens on the SQL Server is a Transaction. You can group T-SQL commands together as one Transaction, but even doing things like DBCC REINDEX will log an enormous number of records in the Transaction Log as it records everything done in the re-index process. Probably the best way to imagine it is that the Transaction Log contains a record of everything the SQL Server does, so that it can be un-done or re-done if the need should arise. For example, if the database crashes in the middle of a Transaction, SQL Server will have to either complete what it was attempting to do, or un-do everything it did so that the database is not corrupted. The Transaction Log allows SQL Server to go back through every step it has done since the last Full Backup, and make sure the database is not corrupted.

So, the internal size of the “.LDF” file is not determined by how much data is in your database, but by how much the database has changed since the last transaction log backup. So, doing things like re-indexing the entire database will not add new data to the database (unless you increase the page freespace), but it can fill up the Transaction Log (the “.LDF” file). If the “.LDF” file becomes 100% full you wind up in the same situation you had with the “.MDF” files: if Autogrow is off, the database crashes; if Autogrow is on, the “.LDF” file will expand to make more room until it fills up the hard-drive and THEN it will crash.

I did some experiments the other day, and verified that the Full Backup does not empty the Transaction Log internally. In fact, the Differential Backups do not empty the Transaction Log either. So, a lot of people - not knowing how this works - will do only Full Backups and their Transaction Log will just keep filling up until the database crashes (which can take hours if you have an extraordinarily active database – weeks would be more typical though). Often, people will try and remedy this situation by Truncating the Transaction Log internally. This “essentially” is a way of forcing the “Full” Recovery Model to become the “Simple” Recovery Model and Microsoft strongly recommends against this (rather, you should just set the database to “Simple” Recovery Mode in the first place, if this is what is required).

Simple Recovery Mode

There are two options to prevent the Transaction Log from growing out of size internally (three actually, but Microsoft recommends against Truncating the Transaction Log’s internal size except in emergencies). The first is to put the database in “Simple” Recovery Mode. What this does is to cause SQL Server to throw away the information about the Transaction as soon as it is done working with it. In other words, as soon as the Transaction is Committed in the database and Transactional Replication is finished with it, it get’s thrown away. In this case, you cannot do Transaction Log Backups because there is nothing in the Transaction Log to backup. You will still have a Transaction Log in your “.LDF” file, but it will be kept to the smallest internal size possible by containing only Transactions in progress.

Generally this “Simple” Recovery Model is not what you want. The reason being that, if the database crashes, you can only recovery from your last Full Backup. So, if it’s been 24 hours since your last Full Backup, you have lost 24 hours worth of work in the database. For some situations, this may be fine, which is why the “Simple” Recovery Model exists. For example, a read-only database that has data that only changes once a month can afford to do a Full Backup immediately after it is loaded each month and then lose all the changes since that time (which should be none).

Full Recovery Mode

For most people, the “Full” Recovery Model is a much better option. In the “Full” Recovery Model, you use the Transaction Log to track everything that happens in the database, which allows you to restore the database right up to the moment before the crash, so that only Transactions that were in progress, but not Commited, will be lost. And those should return error messages back to the users, letting them know that what they were trying to do did not work. So, essentially, nothing is lost.

When you go to restore the database after a crash under the “Full” Recovery Model, you restore the database using the Full Backup. But then you can use the Transaction Log (the “.LDF” file) to restore right up to the moment of failure. This is why you want your “.LDF” file to be on a separate hard-drive from the database (well… performance is also a big reason); that way, even if the database drive is lost, you can restore using the Full Backup and the “.LDF” file.

Now, in the “Full” Recovery Model you will have a couple problems if you run it the way I just suggested. The biggest problem is that the whole server could be lost due to fire, flood, etc. Another problem is that the hard-drive can only hold so much before it becomes full, and the “.LDF” file can fill up and cause the system to crash. So, that’s where the Transaction Log backups come in. By doing Transaction Log backups, you can move your Full and Transaction Log backups to off-site storage where your whole building can burn down and your backups will still be safe.

The other really important thing to note here is that Transaction Log backups move the Transactions out of the Transaction Log and onto the Transaction Log Backup (which could be on Tape or another hard-drive). So, running Transaction Log Backups regularly will keep your Transaction Log (“.LDF” file) emptied out internally, because it takes all those Transactions in the Transaction Log that are Committed (and would be thrown away in “Simple” Recovery Mode) and it moves them to the Transaction Log Backup (tape or file). So, when you go to restore, you restore the last Full Backup, then you restore ALL of the Transaction Log Backups since the last Full Backup, and then (if you have it still) you can apply the “.LDF” which contains all Transactions that never got backed up. This will restore the database right up to the moment before the crash.

The key difference between Full/Differential Backups and Transaction Log Backups is that Full/Differential Backups are essentially a copy of the database, where a Transaction Log Backup is just all the old/completed Transactions from the Transaction Log. And the Transaction Log is a list of everything that changed (Transactions) in the database which can be used to repeat those changes for the purpose of restoring the database up to a specific point in time.

So, I’d like to make it a point to mention again that the Transaction Log Backups keep the Transaction Log from filling up and requiring the “.LDF” file to grow (in order to contain all the Transactions). The Transaction Log Backups do this by moving the Transactions out of the Transaction Log and into the Transaction Log Backup (which is why you have to restore every single one of the Transaction Log Backups in chronological order before you put the “.LDF” back into use). I should also mention that this “Full” Recovery is what they call “Point-in-time” recovery, because you can restore right up to the moment of failure, or any other time during the day you like – say… 10 minutes before an overzealous DBA deleted ALL the records in an important table even if that was 6 hours ago. You could even restore a copy of the database 10 minutes before the DBA dropped a critical table to another instance of the database, and then use that to copy the table back into the current database without otherwise disturbing the current database. (And of course deleting the second copy once you were done with it, thereby disturbing the actual database as little as possible.)

Differential Backups

Now at this point, you may have noticed that you are restoring about 100 Transaction Log backups, in order to get the system back up and running. That’s where Differential Backups come into play. Essentially, the Differential Backup is a “partial” Full Backup. What I mean is that the Differential Backups only record the parts of the system that have changed since the last Full Backup. The benefit of this is that now you can skip a good portion of those Transaction Log Backups when you go to restore. Besides just being easier, this allows you to get things back up and running more quickly (not to mention that Differential Backups do less work than a Full Backup, and so they run faster). Since the Differential Backup acts like a Full Backup, you would then only need to restore Transaction Logs since the last Differential Backup, rather than since the last Full Backup (Full Backup + Most recent Differential + all Transaction Log Backups since the differential + the ".LDF" if you have it still). Additionally, the Differential Backups are faster then doing a Full Backup, because they are only recording changed parts of the database rather than all parts of the database. Because of this, You have to restore the Full Backup to get what did NOT change before you restore the Differential Backup. Again, Differential and Full Backups record the actual database contrasted to Transaction Log Backups which do not record the database at all, but instead record the transactions as it removes them from the Transaction Log.

To sum it up: in order to restore in “Full” Recovery Mode, you restore the last Full Backup, then you restore only the last Differential Backup, then you restore ALL of the Transaction Log backups since the last Differential backup, and then you can use the “.LDF” file (if it’s still available).

Full Backups contain everything at the moment the Full Backup was taken (except open Transactions) but doing them does not empty the Transaction Log. The Transaction Log Backups remove the Transactions from the Transaction Logs and back them up to a safe place, thereby emptying the Transaction Log. And the Differential Backups are faster to perform than Full Backups, but do NOT contain the parts of the database that have NOT changed since the last Full Backup (Ok. That’s a double negative. Differentials are faster because they only contain parts of the database that have changed since the last backup. But the point is that the Differential backups allow you to skip the restoring of the Transaction Log backups between the last Full Backup and the Differential Backup, but you have to restore the Full Backup first.) Differential Backups do not remove Transactions from the Transaction Log (just like Full Backups which are similar).

If you are doing Transaction Log Backups often (maybe every 15 minutes or every hour), your Transaction Log should not fill up internally and cause the file to need to grow in size. The big exception to this is that: open Transactions will not be backed up. So, you could have one very long Transaction fill up the entire Transaction Log, but since it is still in progress, the Transaction Log Backup will not remove it. This could easily happen if you are importing large amounts of data from another system as one Transaction (This is usually done with flat files); and this is why the “Bulk” Recovery Model exists. Unless you have this type of situation, it’s probably best not to worry about the “Bulk” Recovery Model at this point; just know that it’s somewhere between “Full” Recovery and “Simple” Recovery.

In Conclusion

The ".MDF" files are your database. The ".LDF" files are a record of every transaction that has occurred in the database used to make certain that the database can be recovered in the event of a crash.

The "Simple" recovery model, will throw these transactions away once they are committed in the database and no longer being actively used. This means that you cannot have point-in-time database recovery, and can only recover from Full Backups, no other type of backup is usable in the Simple Recovery Model.

The "Full" recovery model, allows you to do point-in-time database recovery to restore the database to any moment in time, including the instant before the dreaded crash if you still have the ".LDF" file (ie the drive with the ".MDF" file is lost, but the ".LDF" was preserved on a separate drive). Otherwise, you can recover up to your most recent backup.

To restore such a backup chain, you restore the Full Backup, then the most recent Differential Backup, and then all Transaction Log backups since the Differential, and then the ".LDF" file if you still have it.

Doing Transaction Log backups removes the transactions from the ".LDF" file (transaction log) and helps to prevent it from filling up or growing larger until it runs out of space and crashes the database.

Backups are as important as the system itself. Treat them as if your job depends on it, because quite frankly, it probably does.

Is This A Good Question/Topic? 1
  • +

Page 1 of 1