Log File continue growing

Question

Post reply

Log File continue growing

Viewing 15 posts - 16 through 30 (of 40 total)

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

datkop SSC Enthusiast Points: 199 More actions · Answer 1

Thanks Jeff.

So i did the following steps:

Changed Recovery Mode to Simple
Did a checkpoint
Changed back to Full
Did a backup of the Database (.bak)
Did a backup of the Transaction Log (.trn)

When i use DBCC LOGINFO there are still all results with status 2. did i something wrong?

edit: log_reuse_wait_desc was before and after NOTHING, while the backup on BACKUP_LOG status

This reply was modified 1 year, 7 months ago by datkop.

Attachments:

You must be logged in to view attached files.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

My apologies... I don't use any of the things that use the log file of a database so I don't actually know what else to check on this issue. My SWAG would be that some form of mirroring or log file shipping or ??? is either still active or is somehow "stuck". Because I don't use such things, I'm out of ideas.

Hopefully someone else has some more ideas.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

This does look like something maybe outside of SQL Server?!

Although, in order to truncate the log, a checkpoint must be taken after backup. So step 4b. should be a CHECKPOINT, just to be sure.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

deubel_m SSC-Addicted Points: 465 More actions · Answer 4

@datkop Hiho, you missed one important point. You didn't shrink your transaction log file. After that continue to backup the transaction log and check if your log still grows.

datkop SSC Enthusiast Points: 199 More actions · Answer 5

@scottpletcher Tried it but it also doesnt work... Still all VLFs with status 2

@deubel_m Shrinking doesnt help, because VLFs still with status 2 so they will not get truncated

This reply was modified 1 year, 7 months ago by datkop.

deubel_m SSC-Addicted Points: 465 More actions · Answer 6

datkop wrote:

@deubel_m Shrinking doesnt help, because VLFs still with status 2 so they will not get truncated

Of course it will work. Put your database in simple mode, shrink your log file and go on. That's my daily work, works always and that's the way to shrink a log file. This is the way.

This reply was modified 1 year, 7 months ago by deubel_m.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 7

When you use DBCC LOGINFO - how many VLF's are showing and what is the size of each VLF? What do you have defined as the autogrowth setting for the log file?

I see several people have recommended switching recovery model to SIMPLE, shrink the log file, switch back to FULL and perform a full backup. Although that works it should only ever be done as an emergency operation since it breaks the log chain and will prevent restoring across that break.

The appropriate method to shrink a log file for a database in bulk/full recovery model is:

Backup the transaction log
Shrink the log file using DBCC SHRINKFILE
Repeat as needed until log file is an appropriate size

And remember, the autogrowth setting and the size of the log will determine how many VLF's are created.

deubel_m wrote:

Of course it will work. Put your database in simple mode, shrink your log file and go on. That's my daily work, works always and that's the way to shrink a log file. This is the way.

If this is your daily work - you are doing something wrong and potentially putting your organization at risk. If you ever have a problem with the current full backup and have to resort to using a prior full backup to recover you are going to find out the hard way that you are not able to recover the system to a current point in time.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Just to be clear, the reason I suggested going to the SIMPLE Recovery Model in my example was to, in fact, break the Log File chain in an attempt to cause whatever was preventing the log file truncation after a log file backup, to release its icy grip.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 9

Jeff Moden wrote:

Just to be clear, the reason I suggested going to the SIMPLE Recovery Model in my example was to, in fact, break the Log File chain in an attempt to cause whatever was preventing the log file truncation after a log file backup, to release its icy grip.

Understood - but switching recovery model won't interrupt an open transaction, if that is what is causing the issue. If the database was part of an AG then switching recovery model won't work either because you can't change the recovery model.

Even if the log backups are performed for log shipping - there is nothing in the system to indicate that a log backup file has been shipped. If the database is not mirrored or part of an AG then the VLF's will be marked as reusable once a log backup has been performed and there are no open transactions started in that VLF.

This is not to say I don't and have never switched to simple and back to full - there are situations where that is warranted as a last resort, but it should be noted as a last resort when there is no other option.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Jeffrey Williams wrote:

Jeff Moden wrote:
Just to be clear, the reason I suggested going to the SIMPLE Recovery Model in my example was to, in fact, break the Log File chain in an attempt to cause whatever was preventing the log file truncation after a log file backup, to release its icy grip.
Understood - but switching recovery model won't interrupt an open transaction, if that is what is causing the issue. If the database was part of an AG then switching recovery model won't work either because you can't change the recovery model.
Even if the log backups are performed for log shipping - there is nothing in the system to indicate that a log backup file has been shipped. If the database is not mirrored or part of an AG then the VLF's will be marked as reusable once a log backup has been performed and there are no open transactions started in that VLF.
This is not to say I don't and have never switched to simple and back to full - there are situations where that is warranted as a last resort, but it should be noted as a last resort when there is no other option.

As you said, "understood". That's what I get. I was under the impression that switching to SIMPLE would kill something like AG. Thanks for the clarification.

Also, a full backup isn't actually necessary to recover from a broken log chain. You can do a DIF to do that once you're back in the FULL or BULK LOGGED recovery model. I just didn't want to take any chances with the OP and stuck to the high road there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 11

My concern isn't the advice to switch - it is that some believe that is the accepted method without understanding the risks.

As long as you have an unbroken log chain and an unbroken chain of log backups, you can recover from any set of known good backups (full and diff) to current point in time. Break one of those and you no longer have that ability - so if that diff backup file is corrupted, or the corruption occurred before that diff backup was taken and you have a broken log chain you cannot recover.

Again, there are times when that is the only thing left but it should be a last resort option only.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Ah. Totally get that. I'm thinking that they don't actually have a good backup, though and that this is "one of those times". But, good point.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Jeffrey Williams wrote:

My concern isn't the advice to switch - it is that some believe that is the accepted method without understanding the risks.

Yep... I got that and agree.

Jeffrey Williams wrote:

Again, there are times when that is the only thing left but it should be a last resort option only.

Jeffrey Williams wrote:

Yep... I got that and still agree. I believed that the "last resort option" was needed here.

That being said, have you got a solution to the OP's problem because, when I say "last resort", it also means that it was the only thing I had left.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 14

I have a few questions that need to be answered first. If the OP responds then we can see if there are any available options.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

datkop SSC Enthusiast Points: 199 More actions · Answer 15

@jeffrey williams

417 VLFs showing

File Sizes between some KB and 2-7 GB

Autogrowth / Maxsize: By 10 percent, Unlimited

This reply was modified 1 year, 7 months ago by datkop.