Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

transaction log file full Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 11:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 152, Visits: 195
How do I solve this error please?
Thanks
Post #1378107
Posted Sunday, October 28, 2012 11:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
Can you be more descriptive on this.

DB Recovery mode:
Is Differential Backups been taken every while.

OR.. Do this

DBCC Shrinkfile(Adventureworks2012_Log, 512);
Post #1378111
Posted Monday, October 29, 2012 12:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, July 12, 2014 12:11 AM
Points: 81, Visits: 912
It can be because ur log file is set to restricted growth or drive on which log file is kept is full.
Post #1378120
Posted Monday, October 29, 2012 1:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
Please read through this: 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 #1378127
Posted Monday, October 29, 2012 1:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
Thanks mahesh:
Lets say if the drive is full, what options do we have apart from growing the mount point capacity.

Or is there a way to match the LSN here? the tran log backup is failing because the log space full.

how can I change the log_reuse_wait to 'NOTHING' from 'Replication'
Post #1378128
Posted Monday, October 29, 2012 1:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
Mac1986 (10/29/2012)
Thanks mahesh:
Lets say if the drive is full, what options do we have apart from growing the mount point capacity.

Or is there a way to match the LSN here? the tran log backup is failing because the log space full.

how can I change the log_reuse_wait to 'NOTHING' from 'Replication'


Please don't post in multiple threads for the same problem. I answered you in the thread you started, please keep the discussion there for simplicity and clarity.



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 #1378130
Posted Monday, October 29, 2012 2:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
Hello Master Gail: Thanks a million for such beautiful article on log behavior.

Quick Question: I have seen a situation where the there is no replication setup on the DB . CDC is wnabled. and the log_reuse_wait_desc shows as 'Replication'..

As per the article, undistributed transaction is causing the Log Growth. how can i check if which undistributed LSN ((43831:51:1) in my case) provided in DBCC OPENTRAN output is pointing to DDL operations of type ALTER TABLE.

how can i relate and compare the LSN in OPENTRAN with DDL Operation
Please have your comments on this..
Post #1378158
Posted Monday, October 29, 2012 3:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
Mac1986 (10/29/2012)
Hello Master Gail: Thanks a million for such beautiful article on log behavior.

Quick Question: I have seen a situation where the there is no replication setup on the DB . CDC is wnabled. and the log_reuse_wait_desc shows as 'Replication'..

As per the article, undistributed transaction is causing the Log Growth. how can i check if which undistributed LSN ((43831:51:1) in my case) provided in DBCC OPENTRAN output is pointing to DDL operations of type ALTER TABLE.

how can i relate and compare the LSN in OPENTRAN with DDL Operation
Please have your comments on this..


Please don't post in multiple threads for the same problem. I answered you in the thread you started, please keep the discussion there for simplicity and clarity.



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 #1378162
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse