Backup strategies in SQL Server 2005

Question

thanks in advance for any help you can offer me on this topic. This is my first serverfault post coming over from stackoverflow.

I have been setting up at work a SQL Server instance for an installtion of an app that we sell that ideally we want to be leave pretty much on its own. To that end i setup a number of maintenance plans that run backupts, purge log tables, and rebuild indexes etc.

All of this works fine but i'm a bit confused about the way the backup is working. I went for a full blown weekly full backup with nightly differential backups and hourly transaction log backups. All was working fine but as use of the system has ramped up, i've noticed that the hourly transaction log backups are getting huge. Up to and sometimes way beyond 2GB while the full backup only takes 90MB. This may be normal but to me it seems odd. As far as i understood a transaction log backup is supposed to have a very small impact on the server, but how can that be, writing 2GB of data out is never going to be that fast an operation. And of course the size of these logs is causing disk space issues (the spec of the server is stingy - not my choice) and we have had some loss of service.

So two questions really, why are transaction log backups so big, and am i likley doing something wrong.

Secondly, should i bother with transaction log backups, why not just go for hourly differential backups as they are only about 20MB in size.

If anybody fancies pointing me in the direction of some good reading on the subject of backups i would also appreciate that. I do have a good book, SQL Server 2005 unleashed, but i probably need a more details book on backup to really get the information i need to design an appropriate backup plan.

Answer 1

One of the reasons that causes this behavior is when you have a database performing lots of updates, inserts & deletes. In your case, you're overall database may not grow in size if data is only being updated or rows are being inserted/deleted. Having your database in Full Recovery Model, will mean that sql server needs to maintain a running log of all these changes, which is what enables you to perform point-in-time recovery, but consequentially means your log backups will be large.

Firstly, I would question whether your application really needs point-in-time recovery? Not that this is a decision you can make, it's a business decision. If the business does decide it needs point-in-time then consider taking transaction log backups at smaller intervals & let the powers at be know that you're going to need a lot of storage!

If the business decides it doesn't need point-int time, but wants up-to the minute recoverability then consider using Bulk-logged Recovery Model and taking log backups every few minutes. This may reduce the size of your log-backups, depending on what the application is doing, as certain operations can be minimally logged. This means that when a log backup occurs it is actually copying the data pages of minimally logged transactions.

Of course, if the business is happy with losing a few hours of data or a days worth, then changing to Simple Recovery Model with a combination of full/differential backups is the way to go.