Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stebo
Starting Member
3 Posts |
 Posted - 2008-09-14 : 18:14:58
|
Hi all,I am experiencing an issues with a database (32GB) that no matter what I do is growing up to the max of 30GB within hours of being backed up.As it stands right now, the transaction log seems to start growing at night, at around the time the maintenance plan runs (optimizations run at 22:00, DB backup at 22:30, transaction log backup at 23:00, and integrity checks at 23:30). The transaction log part of the maintenance plan fails more often than not - it is set to delete any files older than 1 day, but for some reason is still leaving two days worth. If the transaction log is full when the tape backup runs (which it usually is) then the database backup fails, citing the full transaction log in the backup log. Nobody knows why the log file is growing at night.I am not too familiar with SQL (you could call me a newbie  ). Would I have to set up a transaction log backup schedule under BACKUP within the Enterprise Manager?Any help would be greatly appreciated!TIA,Stephan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 01:20:50
|
| Are you having some overnight jobs doing large data pumping operations? |
 |
|
|
stebo
Starting Member
3 Posts |
Posted - 2008-09-15 : 09:02:24
|
| No, there are no additional tasks running at night. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-09-15 : 09:24:02
|
quote:
Originally posted by stebo
As it stands right now, the transaction log seems to start growing at night, at around the time the maintenance plan runs (optimizations run at 22:00, DB backup at 22:30, transaction log backup at 23:00, and integrity checks at 23:30).
What optimizations are you running - index rebuilds? These will add to the transaction log. Integrity checks will also do the same. How often are you backing up the t-logs? You should be on a regular schedule (every 15-30 minutes?) if the DB is set to full recovery.Terry |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-15 : 11:44:37
|
quote:
Originally posted by tosscrosby
quote:
Originally posted by stebo
As it stands right now, the transaction log seems to start growing at night, at around the time the maintenance plan runs (optimizations run at 22:00, DB backup at 22:30, transaction log backup at 23:00, and integrity checks at 23:30).
What optimizations are you running - index rebuilds? These will add to the transaction log. Integrity checks will also do the same. How often are you backing up the t-logs? You should be on a regular schedule (every 15-30 minutes?) if the DB is set to full recovery.Terry
Integrity check (DBCC checkdb) works on TEMPDB but i agree with rest. |
|
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2008-09-15 : 18:05:25
|
| As you probably know Backing up the log file truncates the inactive portion of the transaction log.Under the full recovery model or bulk-logged recovery model, if the transaction log is not backed up on regular basis, the backup itself can prevent log truncation. Changing your backup strategy to backup the transaction log more frequently should free up enough free space to permit the Database Engine to truncate the transaction log to the point where there is free space for new log records. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-09-16 : 09:46:12
|
| I would recomend making the log backups more frequent***************************Life is for having Fun, and then a little work |
|
|
|
stebo
Starting Member
3 Posts |
Posted - 2008-09-16 : 17:27:45
|
| Thanks for all your suggestions. I'll keep working at it and let you know the outcome. |
|
|
|
|
|
|
|
|
Transaction Log Issue