Log File how i prevent form growing

nav_student

Helo guyi have SQL 2008 R2.

My database is about 25GB, Recovery Model is Full, and the Log File is set to a Initial Size of 1GB and the Autogrowth By 1000MB to a restricted growth 2097152MB.

In a couple of hours the Log (as the Initial Size) is 4GB. Can you tell me why?

How can i prevent this quick growth?

Thanks for your help

Marije_Brummel

It keeps track of all transactions, creating a salesline and deleting one are two lines, not 0.

nav_student

???

Marije_Brummel

Have you used google or MSDN on how SQL Transaction log works?

nav_student

Hi Mark,

check over the Internet and it seems that my problem is not having a Transaction Log Backup.

Dont confuse truncating the log with shrinking the log.

To TRUNCATE is to remove the transactions in the log that are before the last checkpoint, (the checkpoint being when transactions are flushed to the database itself). This is done using the BACKUP command.

To SHRINK the log is to reduce the actual log file size. This is done using DBCC commands.

I recommend this 2 sources:
http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx



I have search also here on Mibuso and i found this:
With this command, you can check WHY SQL Server cannot free the transaction log:
SELECT name,log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc <> 'NOTHING'; -- nothing means Nothing is blocking the reusing of transaction log space.

With this command, you can see how many virtual log files there are in your DB (you need to run it using the db you want to check):
DBCC LOGINFO;

http://mibuso.com/forum/viewtopic.php?f=33&t=59880&hilit=logs

Marije_Brummel

Google is your best friend. (Or Bing.) 8)

nav_student

One thing that I notice that my Log file as increase is size from 100MB (beacause I have shrink the file, it has 99% of free space) overnight to 13GB.


During the day I have create a Task to do a Transaction Log Backup every single hour.

We have a Maintenance Plan overnight that does a:
Backup Database Task
Transaction Log Backup
Maintenance Database Files (1Day)
Maintenance Log Files (1Day)
Check Database Integrity Task
Reorganize Index Task
History Cleanup

I believe it's the task Reorganize Index Task that is increasing the log files.

This morning I have put the TLog initial size to 15GB (growth: 500MB)

What are your thoughts, about this?

bbrown

I noticed the "Maintenance Database Files (1Day)" and the "Maintenance Log Files (1Day)" in your list. Does this mean you are only retaining backups for 1 day? Might give some thought to a longer retension period.

jordi79

Just change the database recovery model from full to simple. But bear in mind this has implications on the database recovery in event of a sql server crash. But in most cases, this will shrink your transaction log significantly.

bbrown

jordi79 wrote:

Just change the database recovery model from full to simple. But bear in mind this has implications on the database recovery in event of a sql server crash. But in most cases, this will shrink your transaction log significantly.

This with "truncate" the log file. It will not shrink it.