Full Transaction Log - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Full Transaction Log

Author

Topic

Wrangler
Starting Member

35 Posts

Posted - 2011-05-06 : 11:44:32

I have a SQL alert setup for Full Transaction Log and it fired off for a database this morning. The database is in Simple mode with a little 52MB transaction log. The logs drive has 7.7GB of free space on it. The database log is set to restricted growth at 2GB. The event log displays an operating system error of 112. I'm baffled. Any clues?

Thanks,

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-06 : 13:25:38

Is auto shrink enabled?

Could be that the log couldn't grow fast enough. Check the error log for auto-grow timeouts..

--
Gail Shaw
SQL Server MVP

Wrangler
Starting Member

35 Posts

Posted - 2011-05-06 : 13:47:54

Auto shrink is enabled. I don't see any auto-grow timeouts in the logs.

The error message suggested I look at the log_reuse_wait_desc column in sys.database. I did and the value is 4 suggesting ACTIVE_TRANSACTION. I'm not really sure how to get it out of that state or even if it is an issue.

Message
The transaction log for database 'xxx' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 14:18:41

should disable auto-shrink.

use dbcc opentran() to see if you have uncommitted transactions

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-06 : 14:58:40

What happened is that something caused the log to grow to it's limits, that threw the error, then later autoshrink reduced the log size to what you saw.
First things first, disable autoshrink and remove any manual shrink jobs. It's a terrible thing to do to a database and not something that should ever be done regularly.

For the log, maybe take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

--
Gail Shaw
SQL Server MVP

Wrangler
Starting Member

35 Posts

Posted - 2011-05-06 : 22:44:40

Will do. Thanks for the help.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-07 : 05:11:26

First change the model of the database take full backup first then take log backup and then truncate log file

If you dont want to do like that then increase the limit of the log

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-07 : 05:29:36

quote:
Originally posted by Jahanzaib

First change the model of the database take full backup first then take log backup and then truncate log file

What?

Why would he switch to full recovery model just to run a manual truncation (which happens on each checkpoint in simple recovery)
Total waste of time. Not to mention how bad a practice manually truncating the log is when in full recovery.

--
Gail Shaw
SQL Server MVP

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-09 : 14:28:30

In SQL 2008 the TRUNCATE option is removed.

Shrinking log files does not cause fragmentation.

backup your transaction log multiple times (2-3 times), then run the shrink command for the log file..

if you change the recovery model to simple and back to full, you are breaking up your backup sequence.. so be aware of that.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-10 : 03:04:01

Agreed with Dinakar

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-11 : 04:12:31

quote:
Originally posted by dinakarShrinking log files does not cause fragmentation.

True, but repeated shrink/grow will (both file fragmentation and log fragmentation).

Shrinking a log is not as bad as shrinking a data file, but it's still not something that should be done without a good reason and certainly not something that should be done regularly.

If the log is shrunk too small, it's just going to grow again. Depending on the autogrow settings that may cause log fragmentation (excessive VLFs), file fragmentation and it will slow the DB down as the log file grows (it cannot be instant initialised)

--
Gail Shaw
SQL Server MVP

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-11 : 13:00:40

Agree Gail..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-13 : 07:30:30

Agreed Gail

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources