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

Mirroring Log Issue Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 2:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
Hi All,

I have a db with mirroing in place and log is growing about 49GB.....Log is growing since log backup not running as it scheduled. So, I manually ran log backup and tried to shrink....however, it not shrinking. Any idea why it won't shrink the log. The free space for log is 99% free. Please advise.

Thanks,
SueTons.


Regards,
SQLisAwe5oMe.
Post #1412650
Posted Monday, January 28, 2013 2:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 237, Visits: 763
It is likely not shrinking because the active part of the log is at the end.
Run a few checkpoints and back up the log again and you should be able to shrink the log file.

Remember to shrink it all the way down to 0 and then extend it to the desired size.
If the desired size is greater than 4gb, then extend it in 4gb chunks (has to do with avoiding VLFs that are too large).


USE [<db_name>]
GO
CHECKPOINT
GO
DBCC SHRINKFILE (N'<db_name>_log' , 0)
GO
USE [master]
GO
ALTER DATABASE [<db_name>] MODIFY FILE ( NAME = N'<db_name>_log', SIZE = 4194304KB )
GO
ALTER DATABASE [<db_name>] MODIFY FILE ( NAME = N'<db_name>_log', SIZE = 8388608KB )
GO

Post #1412669
Posted Tuesday, January 29, 2013 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
SQLCrazyCertified (1/28/2013)
Hi All,

I have a db with mirroing in place and log is growing about 49GB.....Log is growing since log backup not running as it scheduled. So, I manually ran log backup and tried to shrink....however, it not shrinking. Any idea why it won't shrink the log. The free space for log is 99% free. Please advise.

Thanks,
SueTons.

What does the following return when executed against your database

DBCC LOGINFO()



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

"Ya can't make an omelette without breaking just a few eggs"
Post #1412890
Posted Tuesday, January 29, 2013 7:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
Hi, Thanks for both of your replies.

I have fixed the issue by doing couple of more log backups then I shrank and it worked.

arnipetursson, However, I am curious to learn what exactly you mean by saying this "It is likely not shrinking because the active part of the log is at the end."

I know that each records gets created in a VLF files.

Also, by applying the manual check point, are we forcing the active portion of the log to apply to mdf, and therefore it will become inactive log as well......is that the purpose of applying manual checkpoint? Please advise.

Thanks,
SueTons.


Regards,
SQLisAwe5oMe.
Post #1413418
Posted Wednesday, January 30, 2013 9:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 237, Visits: 763
The transaction log is a circular file ( or a circular list of VLFs).
A tran log that has been growing will have its active part at the physical end of the log file.
When you back up the log all VLFs that only have committed transactions will be marked as free.
The VLFs that contain open transactions will not be marked as free (the active part of the log file).
Post #1413681
Posted Wednesday, January 30, 2013 9:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 237, Visits: 763
Oh and the checkpoint is just something I do out of habit when i am doing any type manual admin task.
Post #1413688
Posted Thursday, January 31, 2013 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
SQLCrazyCertified (1/29/2013)
Hi, Thanks for both of your replies.

I have fixed the issue by doing couple of more log backups then I shrank and it worked.

You have cycled the log back to the beginning of the file. It would have been helpful if you could have run
DBCC LOGINFO()

before doing this.



SQLCrazyCertified (1/29/2013)
arnipetursson, However, I am curious to learn what exactly you mean by saying this "It is likely not shrinking because the active part of the log is at the end."

Had you of ran the command above before taking the log backups you would have seen a number of rows returned with the end records having a status of 2 which indicates the active portion of the log is near the end of the file


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1414205
Posted Thursday, January 31, 2013 9:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
Perry Whittle (1/31/2013)
SQLCrazyCertified (1/29/2013)
Hi, Thanks for both of your replies.

I have fixed the issue by doing couple of more log backups then I shrank and it worked.

You have cycled the log back to the beginning of the file. It would have been helpful if you could have run
DBCC LOGINFO()

before doing this.



SQLCrazyCertified (1/29/2013)
arnipetursson, However, I am curious to learn what exactly you mean by saying this "It is likely not shrinking because the active part of the log is at the end."

Had you of ran the command above before taking the log backups you would have seen a number of rows returned with the end records having a status of 2 which indicates the active portion of the log is near the end of the file


Hi Perry,

I saw your original reply after I fixed the issue, so, I was not able to run your query. I will keep this in mind for next time if I encounter similar issues.

Thanks,
SueTons.


Regards,
SQLisAwe5oMe.
Post #1414214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse