Transactions log ldf log file is getting to Large

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transactions log ldf log file is getting to Large

Author

Topic

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 09:48:12

I am doing full backups every night. I thought it will bring down the trans log file. But it doesn't. The log files its up to 14g already. So what Can I do to bring down the trans log file.

SQL Server 2005

Thanks,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-22 : 09:50:10

backup your logs or set the recovery model to simple

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-22 : 09:52:54

This will help to make it more clear:
http://www.sqlteam.com/article/introduction-to-sql-server-database-backups

No, you're never too old to Yak'n'Roll if you're too young to die.

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 09:56:52

I am backup my log files every hour. My database is set too Recovery model to FULL. I needed it to be set as FULL. So any other ideas?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-22 : 11:18:54

Increase the frequency of the log backups?
Is the log growing, or is it fixed at 14GB?

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 12:07:32

Does one particular "hour" have an exceptionally large backup? If so there are jobs / transactions in that hour that are jacking the size up. Index rebuild for example

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 12:11:46

Thank you webfred for the info.

GilaMonster - I have log as Enable autogrowth and file growth = 512MB and Max file 2g Restricted.

On june 20 I had one transaction log backup file as 14g's. But before and after that day all the transaction log only came out to be about 3k. So I do not know what had happen that day but now I tryed to do a full backup and tranaction log backup and nothing happens. How do I trunc the transaction log? Without changing the recovery model?

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 13:11:45

What if I change my Recovery model from FULL to simple so it will shrink tranactions log and then change it back to Full. Would that work?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-22 : 14:32:32

Yes, it will also break the log chain and prevent point in time recovery or log backups until you take a full backup.

Is the log full? If not, you don't need to switch recovery models. There's also nothing wrong with a large .ldf file.

--
Gail Shaw
SQL Server MVP

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 15:29:55

The log is not full yet but we are expereincing preformance problems.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-22 : 15:38:45

Large log is unlikely to cause performance problems. Is the log space been reused? If not, why not? (check the log_reuse_wait_desc column in sys.databases)

--
Gail Shaw
SQL Server MVP

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 15:45:34

This is what I get inreturn sys.databases.

log_reuse_wait log_reuse_wait_desc
2 LOG_BACKUP

I am little confused about this table. What does this tell me?

leodone
Starting Member

30 Posts

Posted - 2010-06-22 : 16:02:24

log_reuse_wait log_reuse_wait_desc
2 LOG_BACKUP

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-06-22 : 23:43:05

That value tells you that the transaction log file can be reused once it has been backed up.

Try reading through this (http://www.sqlbackuprestore.com/transactionlog.htm) to get a better idea of how the transaction log works, and also how to find out how much of the log is actually being used. I agree with Gail in that a large transaction log is unlikely to cause performance problems. However, if you want to, you can shrink the size of the transaction log file. See here (http://www.sqlbackuprestore.com/truncatingshrinking.htm).

We don't usually recommend users to shrink the transaction log file below a size that's required for the database's daily operations. However, if you say that the 14 GB increase in size was due to a one-off occurrence, it may be ok to shrink the file in this case.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily

leodone
Starting Member

30 Posts

Posted - 2010-06-23 : 10:15:39

Thank you Gail and Ray for the info.

I just got one more question. Do you recommend to go into Database Properties and change Auto Shrink = TRUE or Not?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 10:34:01

No! Autoshrink should NEVER be enabled. In general databases shouldn't be shrunk at all.

--
Gail Shaw
SQL Server MVP

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-06-23 : 23:27:30

Agree with Gail, the Autoshrink should not be enabled. If your transaction log is growing to 14 GB regularly, there must be a reason behind it. Always shrinking it and then getting it to grow again will just cause the file to get physically fragmented badly.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources