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 ««12

Log File Continues to grow Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 10:09 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
you say mirroring was turned off or disabled, but that doesn't mean it was done correctly. If it's not removed from the source, and you don't have a destination, the log will continue to grow.

When you query sys.databases, what is returned for the log_reuse_wait_desc? It should be a number.

If this is 0, then either you have an open transaction, or you are not running log backups as you think you are. Double check everything is correctly configured.

you could try switching to simple mode, then back to full, then take a full backup and shrink the log (DBCC SHRINKFILE). then monitor to see what happens.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1464246
Posted Monday, June 17, 2013 12:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:31 AM
Points: 706, Visits: 1,752
you say mirroring was turned off or disabled, but that doesn't mean it was done correctly. If it's not removed from the source, and you don't have a destination, the log will continue to grow.

When you query sys.databases, what is returned for the log_reuse_wait_desc? It should be a number.

If this is 0, then either you have an open transaction, or you are not running log backups as you think you are. Double check everything is correctly configured.

you could try switching to simple mode, then back to full, then take a full backup and shrink the log (DBCC SHRINKFILE). then monitor to see what happens.


I know that I disabled mirroring about two years ago. I know that both the source and destination do not show mirroring enabled. But, is there another way to verify?

When I run the query :

select log_reuse_wait_desc from sys.databases where name = 'Ultipro_WSI'

Results do not include a number just: LOG_BACKUP

I'll try switching from simple to full right now and then shrink.



Post #1464319
Posted Monday, June 17, 2013 12:34 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:31 AM
Points: 706, Visits: 1,752
..just a follow up..

I was able to shrink the log successfully.



Post #1464321
Posted Tuesday, June 18, 2013 3:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:14 AM
Points: 22, Visits: 127
Hi

I had a similiar issue with an OLTP db on one of my servers, all the symptoms look to be the same. My suggestion is to investigate if the transactions within your log file are being committed or hardened to your data file. The problem I found was that across tran log backups the uncommitted transactions were remaining. The resolution was then to weekly run independantly a TRUNCATE LOG SQL Agent Job, ensuring that uncomitted transactions were rolled back.

Regards,
Ronan
Post #1464531
Posted Tuesday, June 18, 2013 4:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:15 AM
Points: 1,329, Visits: 2,615
ronang (6/18/2013)
Hi

I had a similiar issue with an OLTP db on one of my servers, all the symptoms look to be the same. My suggestion is to investigate if the transactions within your log file are being committed or hardened to your data file. The problem I found was that across tran log backups the uncommitted transactions were remaining. The resolution was then to weekly run independantly a TRUNCATE LOG SQL Agent Job, ensuring that uncomitted transactions were rolled back.

Regards,
Ronan


Manually truncating the log weekly should not really be required. You can always check for the uncommited transactions using DBCC OPENTRAN command.



Sujeet Singh
Post #1464553
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse