February 7, 2011 at 12:37 pm
Hi,
I use DPM to backup my databases and expect that my database tran log will be truncated after DPM does it stuff. But the transaction log isn't truncating and is continually growing until the drive is out of space. My database recovery model is FULL.
I have to alter the database and change recovery mode to SIMPLE, run DBCC SHRINKFILE and alter database back to FULL recovery mode to shrink the tran log. I run this in a nightly scheduled job after DPM express full backup should be completed.
Just wondering if anyone else using DPM has experienced this and if you have any insights?
Thanks
Jill
February 7, 2011 at 1:04 pm
Backing up the database does nothing to free up space in the transaction logs. You have to back up the logs specifically to allow them to create checkpoints and overwrite space inside themselves.
What you're describing is the normal, expected behavior of a database backup.
The question you need to answer is, Why is the database in Full recovery model? Are you using that to allow point-in-time restoration from backups? If so, you need to back up the log during the day. If you aren't planning on allowing for point-in-time restores, which sounds likely since you're just backing up daily and truncating the log, then change to Simple recovery and leave it that way.
Doing a log truncate after the database backup breaks the backup chain anyway, and means you won't be able to do point-in-time restore. You'll only be able to restore from the nightly database backup, and will lose any data since then. That's your current situation. If losing data is allowable, then set the database into Simple mode and leave it there.
If you can't lose the current day's data, then you need to put it in Full recovery, leave it there, and run periodic log backups. Many DBAs use hourly log backups, some more frequent than that and some less. You'll need to determine the needs for that. This can prevent most usual forms of data loss.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply