SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log space full 100%


Log space full 100%

Author
Message
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1357 Visits: 791
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...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203772 Visits: 41949
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1357 Visits: 791
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211687 Visits: 46258
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, 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


Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1357 Visits: 791
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211687 Visits: 46258
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, 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


Mac1986
Mac1986
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1357 Visits: 791
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211687 Visits: 46258
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, 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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203772 Visits: 41949
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203772 Visits: 41949
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search