May 19, 2015 at 4:31 pm
To reduce the VLFs. I saw when the log file is large it has more no of vlfs
May 20, 2015 at 12:06 am
ramana3327 (5/19/2015)
To reduce the VLFs. I saw when the log file is large it has more no of vlfs
Well, yes, of course it will. VLFs have a certain max size so larger logs have to have more of them.
Still why are you intent on shrinking? You're not gaining anything, the log grows, you shrink it, it grows, you shrink it, etc. Lots of wasted effort and time and you're not gaining long term.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 12:06 am
as said earlier .. check the reason of log not shrinking
in log _reuse_wait column
and to reduce the no of VLF
keep the autogrowth to approx of 1 Gb in Your Case
as less the autogrowth size higher the count of VLF
May 20, 2015 at 12:15 am
Almighty (5/20/2015)
as said earlier .. check the reason of log not shrinkingin log _reuse_wait column
Log_reuse_wait has nothing to do with the log not shrinking. It tells you why the log isn't getting reused. Big difference. The log will not shrink automatically and regular, manual shrinks are a bad idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2015 at 12:30 am
GilaMonster (5/20/2015)
Almighty (5/20/2015)
as said earlier .. check the reason of log not shrinkingin log _reuse_wait column
Log_reuse_wait has nothing to do with the log not shrinking. It tells you why the log isn't getting reused. Big difference. The log will not shrink automatically and regular, manual shrinks are a bad idea.
yes gail atlist he will get to know why the log is not shrinking even though log backup is done
May 20, 2015 at 12:34 am
Almighty (5/20/2015)
GilaMonster (5/20/2015)
Almighty (5/20/2015)
as said earlier .. check the reason of log not shrinkingin log _reuse_wait column
Log_reuse_wait has nothing to do with the log not shrinking. It tells you why the log isn't getting reused. Big difference. The log will not shrink automatically and regular, manual shrinks are a bad idea.
yes gail atlist he will get to know why the log is not shrinking even though log backup is done
Again, with feeling, log_reuse_wait has nothing to do with the log not shrinking. It tells you why the VLFs are not getting reused. There is a big difference here because if the VLFs are not getting reused you get LOG GROWTH.
May 20, 2015 at 12:17 pm
Almighty (5/20/2015)
GilaMonster (5/20/2015)
Almighty (5/20/2015)
as said earlier .. check the reason of log not shrinkingin log _reuse_wait column
Log_reuse_wait has nothing to do with the log not shrinking. It tells you why the log isn't getting reused. Big difference. The log will not shrink automatically and regular, manual shrinks are a bad idea.
yes gail atlist he will get to know why the log is not shrinking even though log backup is done
That's easy. It's because log backups DO NOT shrink the log file. They're not intended to shrink the log file, they mark the space within the file as reusable.
Log_reuse_wait describes why a VLF could not be marked reusable, nothing whatsoever to do with shrinking the log.
Maybe take a few minutes and have a read through http://www.sqlservercentral.com/stairway/73776/?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 12:12 pm
Thank you.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply