Question regarding Log issues with Mirroring database.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Question regarding Log issues with Mirroring...

16 posts, Page 2 of 2

««1 2

Question regarding Log issues with Mirroring database.

Rate Topic

Display Mode

Topic Options

Author

Message

Lynn Pettis

Posted Friday, March 15, 2013 4:03 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588, Visits: 27,384

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.

Cool

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

Lynn Pettis

Posted Friday, March 15, 2013 4:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588, Visits: 27,384

Speaking of Gail, looks like I did interpret it correctly.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1431825

GilaMonster

Posted Friday, March 15, 2013 4:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Lynn Pettis

Posted Friday, March 15, 2013 4:14 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588, Visits: 27,384

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.

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1431830

SQLCrazyCertified

Posted Friday, March 15, 2013 4:41 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:23 PM
Points: 312, Visits: 1,856

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.

Post #1431835

Perry Whittle

Posted Saturday, March 16, 2013 7:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201, Visits: 11,150

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

Post #1431897

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions