February 20, 2007 at 10:19 am
What are example scenarios where the transaction log backups are larger than the actual transaction log?
Thanks.
February 20, 2007 at 11:19 am
Not encountered one yet.Why the question?
February 20, 2007 at 11:30 am
I've inherited some servers that are doing periodic transaction log backups. They've been failing for weeks because there's no more diskspace. I decide to cut my losses, and delete all old transaction logs and start from a clean slate.
But when the next log backup occurs, the resulting backup is bigger than the actual log. I'm not sure how that can happen.
February 20, 2007 at 11:54 am
First I would check my OS .Traditionally backups are not bigger than the source files. And the way transaction logs are supposed to be backed up(Inactive sections) it is hard to understand how your transaction log will be bigger than your log file.
How often are you backing up your transaction logs?
February 20, 2007 at 3:48 pm
Empty space. Lets say I create a file (and it doesn't matter if it is the data or log file)....and I make it 100 MB. Now I fill it with 25 MB of data (or log transactions). The file is still 100 MB, even though 75 mb are free space. When you do a backup, it has to back up that empty space also. That's why it's good to do a shrink file before doing backups.
-SQLBill
February 21, 2007 at 6:25 am
I dont think Empty space is the reason. Empty space would mean the the actual file size should be bigger than a backup. but here it is other way round. Something strange is happening which should not. I hope that data and log file drives are not compressed.
Would like to know the reason if any one finds it out.
February 21, 2007 at 7:25 am
within reason no real issues, if you drop and recreate a clustered index the logging should be around 1.25 - 1.5 times the size of the table. Or a data load which deletes or drops the target table before a load would likely generate a very large log ( I know as I have this scenario ) , which would be larger than the size of the imported table. creating temp tables ( working tables ) in the database or users xcreating their own objects and then dropping them. sp_updatestats etc. etc. If you don't do any backups of the logs during a series of maint tasks or loads you could get a log bigger than the database.
It's only a problem if it causes you problems, if it's just an event which happens then work to reduce it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 21, 2007 at 7:28 am
you also need to check your backup job, I've seen backup jobs which shrink the log, that disguises the fact the log was growing, as well as adding fragmentation. The empty space doesn't get backed up or affect the size of the backup, only the data.active part is backed up. EM might not be telling the truth, run dbcc updateuage(0)
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 21, 2007 at 8:44 am
Thanks for all the input. As I mentioned, the log backups have been failing for weeks, and after making enough room, only the next transaction log was "oversized." Subsequent logs have been on par.
To address some points, I don't believe empty space is captured in backups either and I'm running a standard SQL maintenance plan for my log backups.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply