January 8, 2008 at 2:55 am
I have a db maint plan set to do a full backup once a week (Sunday 12:00) and transaction logs every other day (M-Sa 2:00). Why is my first trans log backup larger than my full backup? It seems that the first trans log backup is a full backup + a trans log backup.
12/26/2007 02:00 AM 257,536 dbSecRpt25_tlog_200712260200.TRN
12/27/2007 02:00 AM 257,536 dbSecRpt25_tlog_200712270200.TRN
12/28/2007 02:00 AM 257,536 dbSecRpt25_tlog_200712280200.TRN
12/29/2007 02:00 AM 257,536 dbSecRpt25_tlog_200712290200.TRN
12/30/2007 12:12 AM 2,925,525,504 dbSecRpt25_db_200712300010.BAK
12/31/2007 02:01 AM 3,090,391,552 dbSecRpt25_tlog_200712310200.TRN <==== this one
01/01/2008 02:00 AM 257,536 dbSecRpt25_tlog_200801010200.TRN
01/02/2008 02:00 AM 257,536 dbSecRpt25_tlog_200801020200.TRN
01/03/2008 02:00 AM 257,536 dbSecRpt25_tlog_200801030200.TRN
01/04/2008 02:00 AM 257,536 dbSecRpt25_tlog_200801040200.TRN
01/05/2008 02:00 AM 257,536 dbSecRpt25_tlog_200801050200.TRN
01/06/2008 12:12 AM 2,961,832,448 dbSecRpt25_db_200801060010.BAK
01/07/2008 02:02 AM 3,124,939,264 dbSecRpt25_tlog_200801070200.TRN <==== this one
01/08/2008 02:00 AM 265,728 dbSecRpt25_tlog_200801080200.TRN
January 8, 2008 at 3:04 am
The transaction log backup consists of each transaction that you've performed against your database. If you have had many transactions between your full backup and your first log backup then it's going to be big.
Are you doing some database maintenance such as reindexing, defragging, data import, etc after your full backup? If so then these will be the cause of the huge log file.
Think of it in these terms - if you take a full backup, then run a number of update statements that touch each table (effectively rewriting all rows in your database) then your log backup needs to record all these changes. If you take a log backup and then take another one immediately you'll see that your first backup is very big but then your second log backup is very very small as the log is empty.
January 8, 2008 at 7:31 am
On sundays we do a full index rebuild and update stats. I presume that one or both of these operations are being logged then. Perhaps I should move these jobs to Saturdays before the full backup. Or move my full backups to sunday evening instead of sunday morning.
Thanks for the extra eye....
January 9, 2008 at 7:05 am
Chappy, if you are doing one trans backup nightly between your weekly full, why not do a diff backup nightly instead? Also if you are only doing the trans backup once a day can you afford to loose one day's worth of data? If not they see about adding trans backups several times a day.
January 9, 2008 at 7:24 am
I would suggest that you take a final log backup for the week, then do your maintenance work then start your backup cycle again with a full backup. I only suggest this because you'll not be having a bloated transaction log and you will still have a backup to resort to if something goes awry during your maintenance.
The suggestion for differential backups is not a bad one - depends on the amount of data changing. You could do a differential each night and several transaction log backups during the day I suppose. It all depends on how many files you want involved in the restore operation should it ever be needed and, again depending on your data patterns, the diff+trans combination of backups may be smaller/easier.
January 11, 2008 at 9:52 am
I have the same issue too... I did a full backup at 11:30, it has 34,994kb. after 5 mts I took a trn log backup,the size of it is 44,108kb. I haven't run any transactions or maintenance work inbetween that 5 mts.... is there anything else I need to check
January 11, 2008 at 1:31 pm
A full backup does not truncate your transaction log. You must back up your transaction log to truncate it.
This is a quote from BOL
Note Sometimes a transaction log backup is larger than a database backup. For example, a database has a high transaction rate causing the transaction log to grow quickly. In this situation, create transaction log backups more frequently.
Here is the link:
http://msdn2.microsoft.com/en-us/library/aa173551(SQL.80).aspx
The bottom line is your transaction logs are huge is because they are full of data. If you want to reduce the size of the transaction logs, then you have to backup your transaction logs more frequently. Instead of every other day you may want to perform transaction log backups every day.
-Adam
January 11, 2008 at 1:46 pm
I am doing the transaction log for every 20mts. The first log is bigger than the full backup as I Quoted in my last post, after that all the trn files are very small depending on 20mts worth of transactions...
January 11, 2008 at 2:09 pm
I am doing the transaction log for every 20mts. The first log is bigger than the full backup as I Quoted in my last post, after that all the trn files are very small depending on 20mts worth of transactions...
This was intended for the original poster, who is only do log backups every other day. You should probably take a look at the specifics of when your transacion log back is huge. The question you should be asking yourself is: Is the transaction log huge only during certain times of the day? If you can pin point the problem to a time then you can narrow the reason to an activity such as bulk inserts or reindexing.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply