Database Log backup bigger than the database size

Question

Database Log backup bigger than the database size

sivaprasad

SSCertifiable

Points: 6183
More actions
January 28, 2006 at 2:27 am

#115976
Database Log backup bigger than the database size:
The environment is like;
- SQL Server 2000 Standard Edition with SP 3a on Windows 2000 Server.
- Database set with "Full recovery" model.
- Database full backup schedulled on 09:00 PM everyday.
- Log backup schedulled on 11:00 AM and 04:00 PM everyday.
But the log backup of Saturday 11:00AM size only bigger than the database size.
What could cause the database log backup size to bigger than the database size?
How can I find out what action on the database that too on the partcular day at particular time causing this problem?
THA
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You should consider doing more frequent log backups to have the size of the backup file smaller, at least one each 30 minutes. It seems that you have many transactions in your database (or few but very big ones).

run DBCC OPENTRAN('DBName') to check the oldest transaction. If you can't find anything with this, run a trace with profiler to exactly know what is running in your server.

If you don't wont to have more frequent log backups, maybe you should consider putting your db in Simple recovery mode.

sivaprasad SSCertifiable Points: 6183 More actions · Answer 2

Can you just elobrate what are things do I need set with SQL Profiler to capture the transactions which could cause the log file growth.?

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 3

There are several things that can cause a transaction log to grow to huge amounts (yes, larger than the database itself). Mostly, reindexing or defragging the indexes will cause this to happen. Are you doing any database maintenance other than backups?

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Data loads as well. Is something happeneing on Fri night like this? Or maintenance as Bill suggested?

Curtis Tamanaha Right there with Babe Points: 725 More actions · Answer 5

Maybe I don't understand what you are asking but if it is what I think you are asking here a suggestion.

DBCC Shrinkfile

I've noticed that I had a log file that was 4x larger than the db. Someone in this forum (or another?) suggest running this procedure and it reduced the size significantly.

DBCC Shrinkfile ('file_name', TRUNCATEONLY)

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 6

If the huge transaction is not backed up with a transaction log backup, then DBCC SHRINKFILE won't help much, because it is still in the log so it won't free the space.

You can monitor log growth with perfmon during the day and then check the time that it starts growing up. With the time of the day you can later check any maintenance job that runs on that time or check with any analist if there is a process that run on that time.