Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring Log Issue


Mirroring Log Issue

Author
Message
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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.
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 16562
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" ;-)
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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.
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
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).
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
Oh and the checkpoint is just something I do out of habit when i am doing any type manual admin task.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 16562
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" ;-)
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search