Backup transaction log is too big

Jakie_Ripper

Hello,

I've not created a transaction log backup for a few days. When I want to backup the transaction log it will create a file of more than 40gb. That does'nt fit on the disk. Is there a way to reduce this transaction log backup size?

I've truncated the log file, than shrink the log file and then do a full backup. But it doesn't helped me with the size of the transaction log backup file.

There's also a nas running on the db, maybe that's the problem?

Thanks

Jakie

David_Singleton

Jakie_Ripper wrote:

I've not created a transaction log backup for a few days. When I want to backup the transaction log it will create a file of more than 40gb. That does'nt fit on the disk. Is there a way to reduce this transaction log backup size?

40gig in a few days That's a lot of growth. What is the size of your database? Is it normal for your log to grow this fast? Did you maybe restore a backup? (Also define a "few" days")

You could simply truncate the log, but I am not sure if that is a great idea. You need to resolve this issue at the cause.

stryk

Hi!

Well, you could clear the TLog with a BACKUP LOG ... TRUNCATE ONLY command; this will remove all data about committed transactions - Actually you empty the TLog. Afterward you could shrink it to a reasonable size (shrinking is ideally performed when NO transactions are running, ideally disconnect all users/processes).

Of course this interrupts the sequential chain of backups; you have to create a full backup then!

If you frequently encounter problems with backup-space, you could also look into compresion tool, for example "Lite Speed" from Quest: http://www.quest.com/litespeed-for-sql-server/
(or with SQL Server 2008 backups could be compressed "out-of-the-box")
Regards,
Jörg

Jakie_Ripper

Thanks for the info

But I haven't got a problem with the log file, but the backup of the log takes too much disk space.

I haven't done the transaction log backup for 5 or 6 days. The database is approx. 62gb.

I shall disconnect all users (nas users also) then truncate the log, shrink the log and then do a full backup, afterwards I will start the transaction log backup, after that I will start the nas service again.

David_Singleton

Jakie_Ripper wrote:

I've not created a transaction log backup for a few days. When I want to backup the transaction log it will create a file of more than 40gb. That does'nt fit on the disk. Is there a way to reduce this transaction log backup size?

Jakie_Ripper wrote:

But I haven't got a problem with the log file, but the backup of the log takes too much disk space.

I haven't done the transaction log backup for 5 or 6 days. The database is approx. 62gb.

Well if you think so then fine, but if what you posted here is true, then I would say you have a problem.

You have a 62 GIG database, and the log file grows 40gig per week. To me that seems excesive. Are you doing some massive imports that you then delete?

Maybe I am just missing some thing. Maybe Joerg can comment further, he for sure knows more about this stuff than I do.

kine

Using full recovery model and doing "transaction log backup after for 5 or 6 days" is not good. When somewhere full recovery model is used, the transaction log is backed up few times per day (depends on size per day) to keep it small. If the backup is too big, I recommend to analyze your backup strategy and all settings around like used disks for the backups, moving old backups to other location, scripts to checking free space etc.

Jakie_Ripper

Normally the transaction log backup is every hour. But the last days it didn't work. How can I solve this problem so the normal hourly schedule can be runned again?

kine

I thought that Jörg already answered the question...

David_Singleton

kine wrote:

I thought that Jörg already answered the question...

No, actually I asked a different question.

The question was : "Isn't it excessive to have the log file grow 40gig in less than a week on a database that is only 64 gig. To me that just seems like too much.

I think there is more to this issue than just the bad hardware design. In fact maybe the hardware is fine, and the problem lies somewhere else.

kine

No, I think it is not problem, you can be only heavily changing the data, not inserting... don't forget that each SIFT table update will generate the log etc. It means that just simple change of one value on one record can lead to big increase in the transaction log...

David_Singleton

kine wrote:

...you can be only heavily changing the data, not inserting...

Thats why I was mentioning Dataporting, when he said about NAS. Maybe there is a lot of unnecessary transactions going on that could be redesigned.

But you are right that it can be normal. Just probably something that needs to be checked.