January 28, 2013 at 2:09 pm
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.
January 28, 2013 at 2:53 pm
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
January 29, 2013 at 4:07 am
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" 😉
January 29, 2013 at 7:46 pm
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.
January 30, 2013 at 9:22 am
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).
January 30, 2013 at 9:32 am
Oh and the checkpoint is just something I do out of habit when i am doing any type manual admin task.
January 31, 2013 at 9:15 am
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" 😉
January 31, 2013 at 9:21 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply