Full Transaction log

Question

Post reply

Full Transaction log

maechismo_8514

SSCoach

Points: 17510
More actions
March 17, 2009 at 10:07 pm

#198092

Comments posted to this topic are about the item Full Transaction log

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Tom Walters SSC Enthusiast Points: 151 More actions · Answer 1

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

Tom Walters

curlywink Valued Member Points: 60 More actions · Answer 2

What about shrinking the database with replication???

jts2013 Hall of Fame Points: 3226 More actions · Answer 3

Hi Krishna

Very 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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

Tom Walters (3/17/2009)
Krishna - Good article. Question though. What if the logfile expanded to fill the rest of the drive. Then what?

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.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

jts_2003 (3/18/2009)
Hi Krishna
Very 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

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.

michael_sawyer SSC Rookie Points: 37 More actions · Answer 6

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.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

michael_sawyer (3/18/2009)
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.

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.

Harald49 SSC Rookie Points: 31 More actions · Answer 8

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.

Thanks

Harald

saiman li SSC Rookie Points: 35 More actions · Answer 9

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 db

change 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.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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 SSC Veteran Points: 202 More actions · Answer 12

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 SSC-Dedicated Points: 30014 More actions · Answer 13

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 SSC Enthusiast Points: 112 More actions · Answer 14

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)