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

Question regarding Log issues with Mirroring database. Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 4:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
If I interpret it correctly, looks like all of the VLFs in the log file have open transactions. Until all the transactions are committed or rolled back, SQL Server can't mark them as reuseable.

Hopefully Gail or someone with more intimate knowldge of the log files can shed more light.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431823
Posted Friday, March 15, 2013 4:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
Speaking of Gail, looks like I did interpret it correctly.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431825
Posted Friday, March 15, 2013 4:09 PM


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 @ 3:15 AM
Points: 42,994, Visits: 36,148
Lynn Pettis (3/15/2013)
If I interpret it correctly, looks like all of the VLFs in the log file have open transactions. Until all the transactions are committed or rolled back, SQL Server can't mark them as reuseable.


Not necessarily open transactions. Something that's preventing log truncation. Could be:
Open transaction
No log backup
Mirroring
Replication
CDC
Active backup
Checkpoint
or a couple other less common ones



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 #1431827
Posted Friday, March 15, 2013 4:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
GilaMonster (3/15/2013)
Lynn Pettis (3/15/2013)
If I interpret it correctly, looks like all of the VLFs in the log file have open transactions. Until all the transactions are committed or rolled back, SQL Server can't mark them as reuseable.


Not necessarily open transactions. Something that's preventing log truncation. Could be:
Open transaction
No log backup
Mirroring
Replication
CDC
Active backup
Checkpoint
or a couple other less common ones


Okay, okay, my internal voice was saying that, I just didn't say it that way in my post. I am officially chastised on this event. I will endeavour to be more specific and concise in the future. I was right, though, that you would come along and provide guidance.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431830
Posted Friday, March 15, 2013 4:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 415, Visits: 2,538
GilaMonster (3/15/2013)
Exactly what it says, all logical log files are in use, so there are no empty ones. No empty VLFs = no shrink.

Check log_reuse_wait_desc in sys.databases for why the log is not being reused.


Ok....got it, finally it allowed me to shrink, I gues a lot of activities were happening behind. Anyway, thanks for all of your input.

Regards,
SueTons.


Regards,
SQLisAwe5oMe.
Post #1431835
Posted Saturday, March 16, 2013 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 6,350, Visits: 13,671
SQLCrazyCertified (3/15/2013)
GilaMonster (3/15/2013)
Exactly what it says, all logical log files are in use, so there are no empty ones. No empty VLFs = no shrink.

Check log_reuse_wait_desc in sys.databases for why the log is not being reused.


Ok....got it, finally it allowed me to shrink, I gues a lot of activities were happening behind. Anyway, thanks for all of your input.

Regards,
SueTons.

In the future you can use
DBCC LOGINFO

to check the status of the t-log VLFs


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1431897
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse