October 21, 2008 at 12:14 pm
Hi Friends,
The database is not part of replication and recovery mode is full. We also have reconfigured the Adhoc log backup job which runs whenever log increased upto a certain limit- however everytime the log backup job/adhoc log backup job runs it doesn't decrease the log file size and it's keep on increasing day by day.
The datafile size is 3GB, while the log file size reached to 54GB and it shows its 94% used.
My concerns are:
- why the log file is not decreasing after backup.
- why it's keep on increasing.
🙂 Please provide help/solution would be appriciated.
October 21, 2008 at 2:00 pm
Are you doing a transaction backup?
If yes, then it would decrease.
If its a gradual increase its the way it is, if not then your transactions are doing more.
Hope this helps.
October 21, 2008 at 2:00 pm
1.Run the log backup only after you run a full backup
2.Why the log size is increasing??
If the Db is active with many DML's then the log would increase certainly.
If this is not a critical DB and you have issues with disk space then you could go for BULK LOGGED RECOVERY Model, here you cannot do a point in time restore. You can only recover to the point where you had a last full backup.
Hope this helps..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 22, 2008 at 2:02 am
Check that your log backups are succeeding.
What's the result of this query for the DB in question
SELECT name, log_reuse_wait_desc from sys.databases
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
October 22, 2008 at 2:05 am
The_SQL_DBA (10/21/2008)
If this is not a critical DB and you have issues with disk space then you could go for BULK LOGGED RECOVERY Model, here you cannot do a point in time restore. You can only recover to the point where you had a last full backup.
You can do point-in-time recovery with bulk logged most of the time. Only time where you can't is when a bulk operation occurred. Then you can only restore to the end of the log backup. Otherwise bulk logged is the same as full recovery.
It's simple recovery where you can only restore to the last full/diff backup.
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
October 22, 2008 at 7:29 am
Or, if you have done a full db backup and want to just squish your t-log, ensure db backup and set recovery mode to 'simple'.
Manually shrink the t-log (should be 99.9% unused). Then set db recovery back to Full and do another backup.
Once t-log is a more managable size it will backup quickly and reduce the chance of failure and log extensions during the backup job (that may actually be your problem).
Hope this helps 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 23, 2008 at 4:16 am
Thanks all for your inputs.
The database is not a part of replication. We require point recovery thats why the recovery model is Full.
The frequency of full and log backup once daily. its as below:
1. integrity check
2. Log backup
3. Full backup
Above three are in one job and this job runs everyday - one time.
Apart from this there is one adhoch log backup job which run if log size reached at 80% only. In our case even if adhoch log backup job run but it doesn't release space(active part) of log file.
October 23, 2008 at 4:25 am
What is the point of having a point in time policy if the log is backed up once daily before the database. That would only be helpful if the database failed after the log backup or the database backup failed followed by the database and the log backup was still ok.
If you really want to have a half way decent point in time then you need at least one or two log backups during the day. If storage for database (disk) backups is an issue, you could also try differential db backups.
A log wont truncate (inf full recovery mode) unless it has been backed up after a full database backup, so consider switching your maint plan priority.
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 23, 2008 at 5:31 am
All friends, thank you very much for your valuable support.
This issue has been resolved becuase the log backup script mentiond the "NO_TRUNCATE" hence it was happening.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply