Log files and full backup

Question

Log files and full backup

Marius Davidsen

SSC Journeyman

Points: 92
More actions
September 17, 2009 at 12:58 am

#385380

Hi,
I've been having some problems with our logs growing to 6 times the databse it self. I would guess its because the logs are not getting commited. I had this idea that when you do a full backup, it would also commit logs and clean up. But this is not correct? So what do i have to do? do i have to run a log-backup to get rid of the logs?
Our DB is around 8GB's now, logs are growing with almoste 1gb a day. A glitch in our rutines occurd, and now the log files are 65GB. We run full backup every night. Should we run a log-backup every 2-3 hour or so aswell? is there a point in doing this so often? would it be enough to run logbackups druing the weekdays and then run a full every weekend?
Thanks for you response,
Best regards,
Marius

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

You should backup your log files every hour or so. Remember that you keep ur database in full recovery mode only to recover your database to point in time from your log backups. The interval of log backup is the amount of data your company can afford to lose in case of crash of log files. Unless you backup ur log, it wont be truncated and hence it'll keep on growing.

Pradeep Singh

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Gail posted a very explanatory article on this subject !

http://www.sqlservercentral.com/articles/64582/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Marius Davidsen SSC Journeyman Points: 92 More actions · Answer 3

Hi, and thanks for such a quick reply!

I started a log backup job about 2 hours ago now, and its just running the last 20-30 gigs of verify now, so after this is done, i would guess that i should have some more than 300mb of free storage! 🙂

How much hassle is it really to do a Point in Time restore?

Best regards,

Marius

Marius Davidsen SSC Journeyman Points: 92 More actions · Answer 4

Grah, it failed, Several of the DBs was in Simple recovery mode. But, shouldent it commit the logs of those DBs that where in Full recovery mode?

----

Marius

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

- commit and overwrite .. yes , SHRINK ... No !

- things that can cause your log files to grow with simple recovery :

-- long running transactions

-- huge data manipulations (insert/update/delete)

- regarding PIT restores:

You need all log entries !

So you would only need all log backups since the previous fullbackup and off course the fullbackup itself !

It's all nicely documented in BOL 😉