none
Shrink log files

Respostas

  • >Thanks all for the replies. My concern is, I see more than 600 VLFs.

    That is a lot indeed. And actually, shrinking the log file is the most
    probable cause of this issue. (Or else, the log was allocated to
    small, and autogrow was set to a small increment).

    The best way to correct this does indeed involve shrinking the log
    file, as a one-time operation. It should be done during an inactive
    part of the day, and immediately after a log backup. And right after
    that, you have to manually grow back the transaction log to the
    desired size.

    After that, make sure to monitor usage of the transaction log and grow
    it manually (by a large amount at once) at appropriate times. Autogrow
    is a great way to prevent being pages at night, but it should not be
    used as the standard groth mechanism; it's a failsafe mechanism only!


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marcado como Resposta Iric WenEditor quinta-feira, 15 de março de 2012 02:10
    quarta-feira, 7 de março de 2012 07:45
  • On Mon, 5 Mar 2012 16:18:09 +0000, Amydom wrote:

    IS it safe to shrink log files when the logshipping is enabled?

    Safe: Yes.
    Smart: No. Unless the log file has seriously ballooned due to a
    one-time issue and will never grow that big again, AND you really need
    the disk space back. See
    http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more info,


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marcado como Resposta Iric WenEditor quinta-feira, 15 de março de 2012 02:10
    terça-feira, 6 de março de 2012 07:33

Todas as Respostas

  • Yes it is and instructions to perform a shrink can be found here http://msdn.microsoft.com/en-us/library/ms189493.aspx or http://msdn.microsoft.com/en-us/library/ms190757.aspx

    It is not safe though to change the recovery model of a log shipped database to simple mode as that will break log shipping.

    • Sugerido como Resposta WFKinsley terça-feira, 6 de março de 2012 17:52
    segunda-feira, 5 de março de 2012 20:38
  • Yep, also see this great link http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    terça-feira, 6 de março de 2012 06:38
    Moderador
  • On Mon, 5 Mar 2012 16:18:09 +0000, Amydom wrote:

    IS it safe to shrink log files when the logshipping is enabled?

    Safe: Yes.
    Smart: No. Unless the log file has seriously ballooned due to a
    one-time issue and will never grow that big again, AND you really need
    the disk space back. See
    http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more info,


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marcado como Resposta Iric WenEditor quinta-feira, 15 de março de 2012 02:10
    terça-feira, 6 de março de 2012 07:33
  • Thanks all for the replies. My concern is, I see more than 600 VLFs.
    terça-feira, 6 de março de 2012 15:49
  • >Thanks all for the replies. My concern is, I see more than 600 VLFs.

    That is a lot indeed. And actually, shrinking the log file is the most
    probable cause of this issue. (Or else, the log was allocated to
    small, and autogrow was set to a small increment).

    The best way to correct this does indeed involve shrinking the log
    file, as a one-time operation. It should be done during an inactive
    part of the day, and immediately after a log backup. And right after
    that, you have to manually grow back the transaction log to the
    desired size.

    After that, make sure to monitor usage of the transaction log and grow
    it manually (by a large amount at once) at appropriate times. Autogrow
    is a great way to prevent being pages at night, but it should not be
    used as the standard groth mechanism; it's a failsafe mechanism only!


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marcado como Resposta Iric WenEditor quinta-feira, 15 de março de 2012 02:10
    quarta-feira, 7 de março de 2012 07:45