SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Krishna / Full Transaction log / Latest Posts

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 17:49:07 GMT

[quote][b]Paul Paiva (3/18/2009)[/b][hr]Good article and everyone has brought up some good points.[/quote]Thanks for the feedback Paul. [quote]However we should also investigate additional reasons that the log filled up in the first place.[/quote]Yes, definitely but that would need one more article.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 17:43:05 GMT

[quote][b]jfoster (3/18/2009)[/b][hr]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 [i][b]purging[/b][/i] the log file(s), so there is no going back if you didn't back them up.USE [AdventureWorks]DUMP TRAN [AdventureWorks] WITH NO_LOGGODBCC SHRINKDATABASE ('AdventureWorks', 1)[/quote]Thanks for sharing that jfoster.:-). Everyday you learn something new.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 17:37:26 GMT

[quote][b]GilaMonster (3/18/2009)[/b][hr]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.[/quote]Thanks for the feedback Gail. I see your point, probably i should have not mentioned that as my first point. I have outlined couple of steps, I should not have said as that is the first thing you can do. We would not be able to shrink the log file until log files are freed.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 17:36:45 GMT

[quote][b]GilaMonster (3/18/2009)[/b][hr]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.[/quote]I see your point, probably i should have not mentioned that as my first point. I have outlined couple of steps, I should not have said as that is the first thing you can do. We would not be able to shrink the log file until log files are freed.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 17:27:36 GMT

Sorry guys I have missed all the banter :) I was a little busy at work today. I will join the discussion.

RE: Full Transaction log

Roger L Reid — Wed, 18 Mar 2009 14:50:45 GMT

Strangely, although we keep everything in FULL recovery mode and do transaction dumps every few hours, differentials every night, and fulls twice a week, we have some databases (always 3rd party, never our own) whose logfiles simply grow and grow. I end up finding a 100 MB db with a 10 GB log file - and its 99% empty.My theory - and that's all it is - is that those vendors are creating the problems themselves.Many a third party vendor has proven they don't know much about the difference between a database and a sequential file, and they often wrap huge amounts of things in transactions that are NOT; or rebuild huge tables from scratch instead of just updating.There often is no practical way (certainly no reliable scripted way) to clear out the space when the usual cycle of dumps doesn't accomplish it, not without taking the database down. It's a real waste of time and money.Even though I've always believed "never ever use auto-shrink", I'm reconsidering for those troubled dbs. After all, these are 64-bit 64GB 8-way servers with multipath, non-front bus IO. And that auto-shrink rule I've known since the days of the /3GB switch...Like I say - never see it in anything we build ourselves, nor on Sybase.Roger Reid

RE: Full Transaction log

Paul Paiva — Wed, 18 Mar 2009 14:08:15 GMT

Good article and everyone has brought up some good points.However we should also investigate additional reasons that the log filled up in the first place.Such as, a sole process may be filling the log when it does a million row insert. If that process runs daily, then your problem will return daily. That process can be modified to perhaps insert the data in smaller amounts, ensuring the transaction is committed between chunks.

RE: Full Transaction log

joeatwork7 — Wed, 18 Mar 2009 14:00:37 GMT

I guess it depends on how many hands are hitting the server. In the situation where I would say it was acceptable is when others won't need to know what happened. I.e., if you aren't the DBA or acting in some fashion of administration for the server, then you probably shouldn't do it. Likewise, if you are sharing administrative responsibilities with one or more people, you might want to avoid doing it unless you consult with the other members of your group.I wouldn't nessessarily place that as an automated feature, and it should be documented where how and why the process took place, etc...but that goes more to the business model that the department is running under.

RE: Full Transaction log

GilaMonster — Wed, 18 Mar 2009 13:51:07 GMT

[quote][b]jfoster (3/18/2009)[/b][hr]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.[/quote]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

RE: Full Transaction log

joeatwork7 — Wed, 18 Mar 2009 10:23:56 GMT

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.

RE: Full Transaction log

GilaMonster — Wed, 18 Mar 2009 10:02:54 GMT

[quote][code]BACKUP LOG MyImportantDB WITH NO_LOGBACKUP LOG MyImportantDB WITH TRUNCATE_ONLYDUMP TRAN MyImportantDB WITH NO_LOG[/code][/quote]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.[code]BACKUP LOG AdventureWorks WITH NO_LOG[/code][color="#FF0000"]Msg 3032, Level 16, State 2, Line 1One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.[/color][code]BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY[/code][color="#FF0000"]Msg 155, Level 15, State 1, Line 1'TRUNCATE_ONLY' is not a recognized BACKUP option.[/color][code]DUMP TRAN AdventureWorks WITH NO_LOG[/code][color="#FF0000"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'TRAN'.[/color]

RE: Full Transaction log

joeatwork7 — Wed, 18 Mar 2009 09:41:48 GMT

Gail,In response to your post, let me add that I believe a backup [i]should[/i] 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

RE: Full Transaction log

GilaMonster — Wed, 18 Mar 2009 09:27:45 GMT

[quote][b]GNUZEN (3/18/2009)[/b][hr]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?[/quote]The best practice, in any environment is not to ever truncate the transaction log. That means none of the following:[code]BACKUP LOG MyImportantDB WITH NO_LOGBACKUP LOG MyImportantDB WITH TRUNCATE_ONLYDUMP TRAN MyImportantDB WITH NO_LOG[/code]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.

RE: Full Transaction log

joeatwork7 — Wed, 18 Mar 2009 09:13:49 GMT

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 [i][b]purging[/b][/i] the log file(s), so there is no going back if you didn't back them up.USE [AdventureWorks]DUMP TRAN [AdventureWorks] WITH NO_LOGGODBCC SHRINKDATABASE ('AdventureWorks', 1)

RE: Full Transaction log

alen teplitsky — Wed, 18 Mar 2009 09:04:49 GMT

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

RE: Full Transaction log

GNUZEN — Wed, 18 Mar 2009 08:21:23 GMT

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

RE: Full Transaction log

GilaMonster — Wed, 18 Mar 2009 08:14:48 GMT

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.

RE: Full Transaction log

Paul Randal — Wed, 18 Mar 2009 07:59:41 GMT

See Kimberly's blog post [url=http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx]8 Steps to better Transaction Log throughput[/url] and my blogs [url=http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx]Search Engine Q&A #1: Running out of transaction log space[/url] and[url=http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-23-My-transaction-log-is-full-now-what.aspx]Search Engine Q&A #23: My transaction log is full - now what?[/url]Thanks

RE: Full Transaction log

saiman li — Wed, 18 Mar 2009 07:25:20 GMT

Here is my take on it. (in sql2005, production with recover full mode)DBCC LOGINFO('mydatabasename')see the status column, you can only shrink the "0" rows after the last 2 or what. Because that was what is "free". (The worse case will be something like replication has a lockup and then the transaction log can't recycle and it will fill to the end)If your log hard drive is full. you should buy time and add an extra log file in the other HD.do a checkpoint (that force transactions to write)backup your dbchange recover mode to simple that basically free up the log. (not the size)Then you can detach the extra log file, shrink the size. turn back to Full mode.

RE: Full Transaction log

Harald49 — Wed, 18 Mar 2009 06:36:30 GMT

Hi Krisha,in order to shrink the logfile you recommend the command:"DBCC SHRINKFILE (transactionloglogicalfilename, TRUNCATEONLY)"But BOL for DBCC SHRINKFILE states that:"TRUNCATEONLY is applicable only to data files."Would you please comment on that.ThanksHarald

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 06:06:55 GMT

[quote][b]michael_sawyer (3/18/2009)[/b][hr]I cannot believe that backup was the third option. The first and best option is to create proper maintenance on the database. The only transactions you need are those occuring since the last backup. This keeps the back the logs small.[/quote]Hi michael,I have pointed out options of what we can do, the preference does not go according to the options mentioned. It totally depends on what you can chose as the best method. Obviously, at the first point if we have a good backup plan, frequent transaction logs, we would definitely not land on this kind of situation.

RE: Full Transaction log

michael_sawyer — Wed, 18 Mar 2009 05:58:48 GMT

I cannot believe that backup was the third option. The first and best option is to create proper maintenance on the database. The only transactions you need are those occuring since the last backup. This keeps the back the logs small.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 04:47:29 GMT

[quote][b]jts_2003 (3/18/2009)[/b][hr]Hi KrishnaVery clear article, but I was a bit confused about the first option of shrinking the log - if it is full now, won't shrinkign the physical size mean it is still full afterwards?John[/quote]Hello John,Thanks for your valuable feedback. When you shrink the log file it removes the inactive VLF's to reuse the space made available.

RE: Full Transaction log

maechismo_8514 — Wed, 18 Mar 2009 04:42:03 GMT

[quote][b]Tom Walters (3/17/2009)[/b][hr]Krishna - Good article. Question though. What if the logfile expanded to fill the rest of the drive. Then what?[/quote]hi Tom,Thanks for your feedback. if the log file filled the rest of the drive you can think of any one of the steps mentioned in the article.

RE: Full Transaction log

jts_2003 — Wed, 18 Mar 2009 03:28:08 GMT

Hi KrishnaVery clear article, but I was a bit confused about the first option of shrinking the log - if it is full now, won't shrinkign the physical size mean it is still full afterwards?John

RE: Full Transaction log

curlywink — Tue, 17 Mar 2009 22:40:33 GMT

What about shrinking the database with replication???

RE: Full Transaction log

Tom Walters — Tue, 17 Mar 2009 22:13:10 GMT

Krishna - Good article. Question though. What if the logfile expanded to fill the rest of the drive. Then what?

Full Transaction log

maechismo_8514 — Tue, 17 Mar 2009 22:07:02 GMT

Comments posted to this topic are about the item [B]Full Transaction log[/B]