Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Transaction log


Full Transaction log

Author
Message
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
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.
Paul Paiva
Paul Paiva
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 265
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.

- Paul

http://paulpaivasql.blogspot.com/
Roger L Reid
Roger L Reid
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 166
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

Roger L Reid
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2228
Sorry guys I have missed all the banter Smile I was a little busy at work today. I will join the discussion.
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2228
GilaMonster (3/18/2009)
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.



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.
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2228
GilaMonster (3/18/2009)
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.




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.
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2228
jfoster (3/18/2009)
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)


Thanks for sharing that jfoster.:-). Everyday you learn something new.
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2228
Paul Paiva (3/18/2009)
Good article and everyone has brought up some good points.

Thanks for the feedback Paul.


However we should also investigate additional reasons that the log filled up in the first place.



Yes, definitely but that would need one more article.
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