Log File Size Issue

Question

Log File Size Issue

Jinu43

Ten Centuries

Points: 1237
More actions
December 22, 2012 at 7:09 am

#268512

Hi,
One my DB Log File Grown to large and now user were getting "transaction Log Full" error.
When i checked server looks like Re-oragenize index cause the issue. I stop that job and try to do the log backup which didn't help me to reduce the log file size.
Users were waititng to perfrom activity so i have change that DB recovery model to Simple.
I ran below listed command and find "Replication" in fornt of that DB name.
Does anyone know what Replication mean here because there is no replication set-up on this DB.
select name, log_reuse_wait_desc
from sys.databases
Even i tried to shrink the Log file using tuncate only commmand and regular shirnk Command but not able to shirnk the log file.
Can you please help on this issue.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Please read through these: Managing Transaction Logs[/url] and http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 2

Some times you need to do log backups couple of times in order for the inactive logs to be truncated.

So, do that coupi of time and also you can shrink it afterwards as well.

Let me know how it goes.

Thanks

SueTons.

Regards,
SQLisAwe5oMe.

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

SQLCrazyCertified (12/24/2012)
Some times you need to do log backups couple of times in order for the inactive logs to be truncated.

Not if the DB is in simple recovery, which the OP says it is now in.

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

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 4

OP mentioned he changed the recover model to simple.

So, by doing that automatically truncate the logs? Or does he need to truncate it manually?

SueTons.

Regards,
SQLisAwe5oMe.

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

SQLCrazyCertified (12/26/2012)
So, by doing that automatically truncate the logs?

Yup, as soon as a checkpoint runs. In this case it won't be able to actually truncate the log, due to the replication. (well, the checkpoint will truncate the log and mark no VLFs as reusable)

Or does he need to truncate it manually?

No. You can't manually truncate the log any more.

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

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 6

GilaMonster (12/26/2012)
SQLCrazyCertified (12/26/2012)
So, by doing that automatically truncate the logs?
Yup, as soon as a checkpoint runs. In this case it won't be able to actually truncate the log, due to the replication. (well, the checkpoint will truncate the log and mark no VLFs as reusable)
Or does he need to truncate it manually?
No. You can't manually truncate the log any more.

Thanks Gail.

SueTons.

Regards,
SQLisAwe5oMe.

Mac1986 SSCertifiable Points: 6216 More actions · Answer 7

Hi Gail,

I have quick question here. What if the Log_resuse_wait_desc is in replication state for 1 whole day and the log file size is 500 GB and not coming down since two days. CDC Cleanup job is contonoulsly running and what would be the fater way to reduce the log file size here. Again DB is in Simple recovery mode.

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

Figure out what's preventing the log from being reused (there's either some actual replication or CDC that's not working) and fix that.

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