August 31, 2009 at 9:39 am
In my maintenance plan for a database of around 2.5gb's, I am doing a full database backup every night at 3am. I also do hourly transaction logs, and I am in Full Recovery Mode.
The problem I am running into is that, after performing my full database backup, I get a 2.5gb .bak file, and then the next hourly transaction log is over 3gb. Is there a reason that the transaction log gets so big after a full backup? Is there a way to keep the first log file from being that big?
August 31, 2009 at 9:49 am
What's happening between that really big log backup and the previous log backup? Full backups don't affect the log chain, so the log backup straight after the full will contain the log records since the previous transaction log backup, not since the full.
If I had to guess, I'd say it's an index rebuild.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2009 at 10:26 am
I don't do an Index rebuild every night, only once a week. There shouldn't be too many transactions in that time, during peak hours the trans log only reaches 1/10th that size.
Is it possible that the full backup does an index rebuild? Also, in the backup, I do a "Verify backup integrity". Could this have any effect?
August 31, 2009 at 10:47 am
dagosta (8/31/2009)
Is it possible that the full backup does an index rebuild?
Not unless you have it specified, either as script that executes ALTER INDEX or as a task in the maintenance plan.
Also, in the backup, I do a "Verify backup integrity". Could this have any effect?
No.
There's something happening between the two log backups that's causing a lot of log activity. You're going to have to track down what it is. Profiler (or a server-side trace) may help in this regard.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply