October 28, 2012 at 7:11 pm
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...
October 28, 2012 at 9:05 pm
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
Change is inevitable... Change for the better is not.
October 28, 2012 at 11:12 pm
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...
October 29, 2012 at 1:06 am
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
October 29, 2012 at 3:05 am
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.
October 29, 2012 at 3:09 am
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
October 29, 2012 at 3:18 am
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.
October 29, 2012 at 5:06 am
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
October 29, 2012 at 6:24 am
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
Change is inevitable... Change for the better is not.
October 29, 2012 at 6:28 am
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply