Automatically taming transaction log sizes

[SantaMufasa]

Today, we again encountered the error:

The transaction log for database '<db name>' is full.

To resolve this problem, as we have done in the past, we successfully issued the commands:

backup log <database> with truncate_only 
go 
use <database> 
go 
dbcc shrinkfile (2, 10) 
go

...then did a full database backup of the database in question.


By what method/setting/et cetera can we avoid the transaction log becoming full? How can we keep the size manageable without on-going manual intervention?



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gmmastros]

Have you looked at this faq962-5742


-George

"the screen with the little boxes in the window." - Moron

 

[SantaMufasa]

Thanks, George. After reading the FAQ, I see that despite there being multiple methods for shrinking Transaction Log Files, none of them occur without some sort of manual intervention; is that your understanding, as well?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gmmastros]

No.

There are several things you can do.

You could set the recovery model to 'simple'. With this method, the transaction log will not grow, but you also won't be able to recover to a point in time. If the database goes 'kablooey', you will only be able to restore from the point that you last backed up the database.

Alternatively, you could schedule a full database backup to occur at set times. When you do a full backup, the transaction log is truncated.

I don't necessarily recommend the first method (recovery = simple) unless your database doesn't change frequently. Typically, dba's will schedule a full backup daily with transaction log backups occuring several times per day (or even several times per hour). That way, if the database blows up, you can restore the previous days full backup and then apply the transaction logs up until current.

Does this make sense?



-George

"the screen with the little boxes in the window." - Moron

 

[SantaMufasa]

Yes, it makes sense. But the FAQ said:

16) My Transaction Log is unexpectedly full. How do I empty it?

Answer: There are several ways to empty it. The first is to make a manual full database backup. This usually truncates (or empties) the Transaction Log.

First, why does the FAQ go to the trouble of specifying "manual" full database backup if, in reality, an automatic backup also truncates the transaction log?

Second, if I have a regularly scheduled nightly automatic backup, why do my transaction logs continue to grow?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gmmastros]

You need to backup the transaction log too.

thread962-1372917

I recommend you schedule a job to backup the transaction log regularly.



-George

"the screen with the little boxes in the window." - Moron