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

Log space full 100% Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 7:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
Two of my databases are showing 100 % log space. I have no space left on the disk to increase the log space. the CDC jobs are failing and the select log_reuse_wait_desc, * from sys.databases is set to replication instead of NOTHING.

My DB is in Simple recovery mode.

When should i do here. As of now I'm shrinking the datafiles of other databases to get some space in the disk. Once i get like 30-40 GB i will increase the logspace and and I guess cdc job will truncate my logs.
Please advice ASAP and let me know if you need any other info...
Post #1378071
Posted Sunday, October 28, 2012 9:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
How big are the log files? I ask because the Simple Recovery mode that you're in shouldn't cause them to grow much and I suspect maybe a "runaway" query that had an accidental many-to-many join.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378081
Posted Sunday, October 28, 2012 11:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
Yes I bet its the run away query. The log sizes are 90 and 85 GB. the data file sizes are. 1.4 TB and 900 GB.
the delta run job might have caused the issue.
what can be done here. I'm unable to change the DB to FULL and take a FULL Backup.

What Am I supposed to do to create some space on disk. I need like 70 GB space on disk so that I can increase the log file space and CDC Job will do the rest.

Please suggest...
Post #1378100
Posted Monday, October 29, 2012 1:06 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 @ 5:22 AM
Points: 40,613, Visits: 37,077
No, it won't be a run-away query. Query execution is not logged to the tran log, many-to-many joins do not write to the transaction log of the DB (maybe of TempDB, but that's not the problem here)

Replication as a log wait means you either have replication configured or have CDC running and not completing.

Please read through this http://www.sqlservercentral.com/articles/Transaction+Log/72488/ especially the section on replication. If it's CDC, you need to identify what caused them to start failing and not allow the log to be cleared.

Changing the recovery model to full (or anything else) would not solve this problem, might make it worse though (you'd then need log backups to do what in simple recovery is done manually)

What you can do is temporarily add another log file which you can remove after the problem is fixed



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 #1378129
Posted Monday, October 29, 2012 3:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
I just red the whole article. actually you have provided the same article in the other issue as well.... Anyways

I have created one more log file here gave 50 GB to it and then kicked off my CDC job. After running for 30 mins it failed again...but did not consume the entire 50 GB it only took 8 GB.


Post #1378159
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 @ 5:22 AM
Points: 40,613, Visits: 37,077
You need to identify why that CDC job is failing. That job failing will cause the log to fill because the log space cannot be marked reusable until the CDC job has processed it.


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 #1378161
Posted Monday, October 29, 2012 3:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
On a general note. In the article that you suggested, there is line where its says

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

Sorry for multiple threaded question. I was trying my luck on that thread but could not get my answer there.
Post #1378165
Posted Monday, October 29, 2012 5:06 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 @ 5:22 AM
Points: 40,613, Visits: 37,077
If you have CDC and no replication, then there's technically no 'unreplicated' transaction as it's CDC that's behind/failing, not replication. They use the same method to get transactions, that's why this looks like replication.

As for identifying what the operation was, you could read the transaction log if you're feeling very brave. That's pretty much it unless you had something tracing commands run that you can go back through and look at (opentran gives the SPID)



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 #1378193
Posted Monday, October 29, 2012 6:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
GilaMonster (10/29/2012)
No, it won't be a run-away query. Query execution is not logged to the tran log, many-to-many joins do not write to the transaction log of the DB (maybe of TempDB, but that's not the problem here)

Replication as a log wait means you either have replication configured or have CDC running and not completing.

Please read through this http://www.sqlservercentral.com/articles/Transaction+Log/72488/ especially the section on replication. If it's CDC, you need to identify what caused them to start failing and not allow the log to be cleared.

Changing the recovery model to full (or anything else) would not solve this problem, might make it worse though (you'd then need log backups to do what in simple recovery is done manually)

What you can do is temporarily add another log file which you can remove after the problem is fixed


You're right. I have TempDB on the brain, lately.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378212
Posted Monday, October 29, 2012 6:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
Mac1986 (10/28/2012)
Yes I bet its the run away query. The log sizes are 90 and 85 GB. the data file sizes are. 1.4 TB and 900 GB.
the delta run job might have caused the issue.
what can be done here. I'm unable to change the DB to FULL and take a FULL Backup.

What Am I supposed to do to create some space on disk. I need like 70 GB space on disk so that I can increase the log file space and CDC Job will do the rest.

Please suggest...


Gail is right. I've got TempDB on my mind lately. She's also got more knowledge about CDC than I'll likely ever have so I'm going to defer to the master.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse