SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Transaction log


Full Transaction log

Author
Message
Paul Randal
Paul Randal
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14016 Visits: 1722
See Kimberly's blog post 8 Steps to better Transaction Log throughput and my blogs
Search Engine Q&A #1: Running out of transaction log space and
Search Engine Q&A #23: My transaction log is full - now what?

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414850 Visits: 47140
Your first suggestion for a full tran log is to shrink it. Maybe I'm missing something, but that's completely the opposite of what's needed.

The log is full, ie, there is no free space within the log file. Since there is no free space within the log file, a shrink will find no space to release to the OS. Even if the shrink did find some free space, that'll just make the situation worse.

If the log file is full you need to either reduce the amount of data inside it by either backing the log up or switching to simple recovery, or you need to grow the log file to give it more space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GNUZEN
GNUZEN
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 588
Can we discuss truncate trans. log in replication environment?

Here we have publisher database having tran. log with large size even though we have log backup and full backup in place. What is the best practice to truncate trans. log on publisher database?

I'm looking for step by step instruction here.

thanks
alen teplitsky
alen teplitsky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12432 Visits: 4674
most of my full log issues have been because of replication. for some reason it didn't truncate the log. so the solution is to reinitialize the publication and it almost always works. once or twice we had to delete and rebuild the publication.

on db's where we don't run log backups, we do backup log with no_log on a regular schedule and keep the db's in simple recovery mode
joeatwork7
joeatwork7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
Krishna,

I thought I'd post another alternative which I didn't see in your article. I've used this in the past, with good results. This method allows me to run it on a live database without causing issues. At the same time, I have had issues where the log file never seemed to decrease after a shrinkdatabase...this has had 100% good results. Of course, the disclaimer would be that you are definitely purging the log file(s), so there is no going back if you didn't back them up.

USE [AdventureWorks]
DUMP TRAN [AdventureWorks] WITH NO_LOG
GO
DBCC SHRINKDATABASE ('AdventureWorks', 1)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414850 Visits: 47140
GNUZEN (3/18/2009)
Here we have publisher database having tran. log with large size even though we have log backup and full backup in place. What is the best practice to truncate trans. log on publisher database?


The best practice, in any environment is not to ever truncate the transaction log. That means none of the following:
BACKUP LOG MyImportantDB WITH NO_LOG
BACKUP LOG MyImportantDB WITH TRUNCATE_ONLY
DUMP TRAN MyImportantDB WITH NO_LOG



All of those break the log chain. That means no log backups and no point in time restores after that until a full backup is run.

With replication, a full log is often the result of the log reader not running or running slowly. Either way, the solution is to fix the problem with the log reader. The inactive log entries cannot be removed until the log reader has processed them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


joeatwork7
joeatwork7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
Gail,

In response to your post, let me add that I believe a backup should be done in conjunction with the DUMP TRAN, when used. There are a number of methods that should probably be addressed prior to a DUMP TRAN WITH NO_LOG or some such. However, I would hesitate to say never, after all there is a time and place for anything.

So, I would say the best practices would include addressing issues based on their merits and using the tools required to accomplish the job at hand in the most effective and efficient way within a given time frame.

This is definitely a moot point and there are a varied number of arguments to be made for which methods to use when. I will have to stand by what I said that I thought this method should have been included (perhaps with a bit more detail and disclaimer).

-Joseph Foster
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414850 Visits: 47140
BACKUP LOG MyImportantDB WITH NO_LOG
BACKUP LOG MyImportantDB WITH TRUNCATE_ONLY
DUMP TRAN MyImportantDB WITH NO_LOG



Just an additional point. All three of those were deprecated in SQL 2005 and removed in SQL 2008. From 2008 onwards the only way to remove inactive entries from the log is to either back it up or set the database to simple recovery.

BACKUP LOG AdventureWorks WITH NO_LOG


Msg 3032, Level 16, State 2, Line 1
One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.

BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY


Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

DUMP TRAN AdventureWorks WITH NO_LOG


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TRAN'.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


joeatwork7
joeatwork7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
Gail,

Good point and well taken.

That should probably go along with the best practices: keep an eye out to upcoming versions.

For those using pre-SQL 2008 servers, this would still be in the toolbox if needed and should be used with their scope in mind.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414850 Visits: 47140
jfoster (3/18/2009)
For those using pre-SQL 2008 servers, this would still be in the toolbox if needed and should be used with their scope in mind.


I'd say no, even pre-2008, either back the log up to disk or switch (temporarily) to simple recovery. Both will result in exactly the same log truncation that backup ... with truncate_only does, the first doesn't break the log chain, the second does but at least it's pretty obvious what's actually happening.

The problem I have with backup ... with truncate only is that it's used and recommended without people realising what it actually does. ALTER DATABASE ... SET RECOVERY SIMPLE makes it pretty clear that you're not in full recovery any longer

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search