Log File Size Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Log File Size Issue

Log File Size Issue

Rate Topic

Display Mode

Topic Options

Author

Message

Jinu43

Posted Saturday, December 22, 2012 7:09 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:39 PM
Points: 61, Visits: 190

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.

Post #1399675

GilaMonster

Posted Saturday, December 22, 2012 8:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 37,725, Visits: 29,983

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1399679

SQLCrazyCertified

Posted Monday, December 24, 2012 7:24 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 312, Visits: 1,865

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.

Post #1400016

GilaMonster

Posted Wednesday, December 26, 2012 12:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 37,725, Visits: 29,983

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 2008, MVP
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

Post #1400336

SQLCrazyCertified

Posted Wednesday, December 26, 2012 2:09 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 312, Visits: 1,865

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.

Post #1400371

GilaMonster

Posted Wednesday, December 26, 2012 2:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 37,725, Visits: 29,983

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 2008, MVP
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

Post #1400381

SQLCrazyCertified

Posted Wednesday, December 26, 2012 3:08 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 312, Visits: 1,865

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.

Post #1400392

Mac1986

Posted Sunday, December 30, 2012 3:37 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180, Visits: 512

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.

Post #1401272

GilaMonster

Posted Monday, December 31, 2012 1:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 37,725, Visits: 29,983

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 2008, MVP
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

Post #1401312

« Prev Topic | Next Topic »

Permissions