January 1, 2009 at 6:17 am
I have a 700mb database. It is set to full recovery model. Backups are:
7am / 7pm - full database backup
7:15am - 6:45pm - differential backup every 15 minutes.
Just prior to the full backup, we run a "backup log with no_log", followed by a "dbcc shrinkfile" on the database log file to shrink it to 50mb.
I recently changed this job schedule to run just M-F (it was 7 days a week).
I noticed that the transaction log grew tremendously over the weekend, when there was no activity.
Sunday am, it had grown 350mb (total size 400mb).
Monday am (before the 7am backup), it had grown an additional 400mb (total size 800mb).
So, why is the transaction log growing without any activity on the database?
On a related note, any suggestions for improving the backup routine?
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 1, 2009 at 6:44 am
Excerpt from books online:
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups, such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more frequently than differential database backups, such as every 30 minutes.
Are you sure there is nothing else happening in database over that time? Run profiler for tht time and track what's causing the growth.
MJ
January 1, 2009 at 6:53 am
WayneS (1/1/2009)
I have a 700mb database. It is set to full recovery model. Backups are:7am / 7pm - full database backup
7:15am - 6:45pm - differential backup every 15 minutes.
Just prior to the full backup, we run a "backup log with no_log", followed by a "dbcc shrinkfile" on the database log file to shrink it to 50mb.
I recently changed this job schedule to run just M-F (it was 7 days a week).
I noticed that the transaction log grew tremendously over the weekend, when there was no activity.
Sunday am, it had grown 350mb (total size 400mb).
Monday am (before the 7am backup), it had grown an additional 400mb (total size 800mb).
So, why is the transaction log growing without any activity on the database?
On a related note, any suggestions for improving the backup routine?
Thanks,
Wayne
Please check the error log for any error raised or not?
Bec, i am facing same problem, in my one database broker service was enabled and transaction is continiously open and database log file is frowing more.And if u set database recovery Full the reguraly take transaction log backups.
January 1, 2009 at 7:36 am
MANU (1/1/2009)
Are you sure there is nothing else happening in database over that time? Run profiler for tht time and track what's causing the growth.MJ
The office was closed over the weekend. There are a few jobs that ran during this time:
One job runs every 2 minutes; it runs a query against a "to-do" table. If it finds something "to do", it runs the procedure that does it. Since the office was closed, there was nothing being put into this table. So all that happens is a select statement running every couple of minutes, and the job then exits out.
There is another job that runs nightly. There was nothing for this job to process, so again there was just a select statement run against the database.
Another job rebuilds the indexes on the tables nightly.
Another job runs hourly during the day building an export file for a web process. Again, just select statements against the database.
Another job runs weekly on Saturday, updating some Excel spreadsheets. Again, just select statements against the database.
There was absolutely nothing else going on during this time period.
Notice also that over the course of two days, the size of the log file was bigger than the database file itself.
Thanks for your help,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 1, 2009 at 8:01 am
Another job rebuilds the indexes on the tables nightly.
DBCC DBREINDEX creates new indexes before dropping the old ones. This is done as one atomic transaction, so a lot of free space may be required.
http://www.sqlservercentral.com/Forums/Topic324588-5-1.aspx#bm324905
If it is rebuilding a clustered index it needs enough space to create a new copy of the table, with a little overhead for non-leaf pages this is where the 1.2 x (avg row size) x (number of rows) recommendation comes from. If it is rebuilding a nonclustered index it needs [(avg index row size) + (clustered key size, or rowid size if no clustered index)] x (number of rows). If it is rebuilding all indexes for a table it needs the sum of the requirements for each index. All of these changes must be logged, so a similar amount of free space is require for the transaction logs
MJ
January 1, 2009 at 8:01 am
Another job rebuilds the indexes on the tables nightly.
This is where you're getting your transaction activity.
Rebuilding the indexes includes clustered indexes, which are your data tables. Essentially, nearly every page in the database is being affected and therefore logged to the t-log file.
Your backup plan does not appear to require FULL recovery mode. The whole idea behind the full recovery model is that you can take incremental (t-log backups) frequently with less frequent differential and full backups.
You should either consider changing your backup strategy or switching to simple recovery mode. Either will prevent your t-log file from growing.
For your information about the transaction log, there is a very good article on this site that should help you understand what is going on.
http://www.sqlservercentral.com/articles/64582/
Kyle
January 1, 2009 at 9:42 am
WayneS (1/1/2009)So, why is the transaction log growing without any activity on the database?
On one hand I understand you expect not to have activity at those times but if transaction log is growing you can be certain you got database activity.
On the other hand -in full agreement with other posters - your backup strategy is not correct. If your backup/recovery strategy calls for point-in-time recovery you should be taking transaction log backup in between full backups; please check Microsoft documentation.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 1, 2009 at 9:59 am
A better backup strategy would be to eliminate the differential backups and run transaction log backups every 15 minutes 24x7. This will prevent your transaction log file from growing so much. The twice daily full backups are OK if you have the space for them.
You should also avoid shrinking the transaction log after it reaches a stable size. It just consumes more resources to grow again.
January 1, 2009 at 10:35 am
Michael Valentine Jones (1/1/2009)
A better backup strategy would be to eliminate the differential backups and run transaction log backups every 15 minutes 24x7.
My thought process is that if I did need to do a restore, I could potentially have to do a LOT of t-log backups. The differential backups are taking about 1-2 seconds, even at the end of the day. This way, I would only need to restore, at most, from 2 backup files... the most recent full, and the most recent differential.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 1, 2009 at 11:32 am
WayneS (1/1/2009)
Just prior to the full backup, we run a "backup log with no_log", followed by a "dbcc shrinkfile" on the database log file to shrink it to 50mb.
Why? If you don't want to maintain the log, set the DB into simple recovery. If your DB is in full recovery then you should be doing log backups, not discarding the log records.
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
January 1, 2009 at 11:43 am
The differentials should take longer and longer as you get further from the full backup, be sure you test this to know what your times are. Perhaps test the full and the last diff before the next full for a worst case test.
If you can handle the space from the diffs, that's not a bad strategy, but be sure that you actually have at least two copies of your diffs since if one goes bad, you lose all activity back to the full. Logs can help here, and it's recommended that you include log backups as well. The diffs speed up restores, but what if someone wants you to get to 10 minutes after the last diff? It's a little paranoid, but I'd grab logs as well. They don't impact diffs, you won't need to keep many, and they're an extra insurance policy.
Also, I'd actually clear the log AFTER the full backup, and only if the full backup succeeds. That way you're still protected if something breaks.
January 1, 2009 at 11:55 am
With a database that is less than 1GB in size, performing differentials every 15 minutes shouldn't take a long time at all. How large these backups are going to be is really determined by how much activity is occurring. I really can't see how that is going to be much more than a couple of meg a day - but, the OP should be able to determine this.
Because of this - I would recommend changing the recovery model to simple and forget about the log or transaction log backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 1, 2009 at 10:35 pm
Keep in mind :
1. Take ragular backup
2. Daily check database file sizes.
3. Monitor File growth
4. If u use reindex/rebuilding , Import/Export, Bulk insert, Restore full database
then Shrink database file.
5. Monitor TempDB size. If it growth more , then move tempdb to individual disk.
6. Check any oldest transaction is not open in Database, because sometimes it happen by developers during development.
January 2, 2009 at 1:57 am
WayneS (1/1/2009)The differential backups are taking about 1-2 seconds, even at the end of the day. This way, I would only need to restore, at most, from 2 backup files... the most recent full, and the most recent differential.
Are you aware your recovery strategy does not allow for point-in-time recovery?... In your recovery scenario all transactions after your most recent differential would be gone; can you afford that?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 2, 2009 at 2:43 am
PaulB (1/2/2009)
WayneS (1/1/2009)The differential backups are taking about 1-2 seconds, even at the end of the day. This way, I would only need to restore, at most, from 2 backup files... the most recent full, and the most recent differential.
Are you aware your recovery strategy does not allow for point-in-time recovery?... In your recovery scenario all transactions after your most recent differential would be gone; can you afford that?
I thik he is not taking transactional backup..
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply