Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE
recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE
recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE
recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE
recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database.
Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files across multiple disks and have done so for multiple databases, and you are observing better I/O now because the log has switched to that file on the faster disk, or is more isolated from your other data/log files. In other words, I believe any observed I/O difference is due entirely to other factors and is merely a coincidence, not due to the fact that you added log files alone. SQL Server is explicitly designed to only use one log file at a time - so how could multiple log files possibly improve log write performance, unless the current log file is on faster / more isolated disk? I think you need to provide better empirical evidence (and in doing so you may discover for yourself the true cause of the improved performance).
Please read these posts in full - they were written by a pretty smart guy who worked on the SQL Server storage team for quite some time, so I don't think he's making any of this stuff up for fun:
Also Kimberly Tripp touches on this in a worthwhile article:
Note that none of the 8 steps involves adding a transaction log file. In fact she recommends against it.
There are other perils to having multiple log files, particularly if they are large (think RTO) - and there really is nothing to gain.
Best Answer
This is one of those exceptions where it's probably okay to execute a
DBCC SHRINKFILE
. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:
This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...
If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using: