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.