none
How to reduce log file size after restore

    질문

  • Hi,

    I get database back from live to test environemtn. Database recovery model is FULL on live. After restore on Test, I change the recovery model to simple. Issue is the size of log file. How do I reduce the size of log file in test environment?

    Thanks in advance



    Shady

    2012년 7월 11일 수요일 오후 12:49

답변

  • You can shrink the file  : DBCC SHRINKFILE(<Filename>)

    Although shrinking file is not a good option but you can do it if it required.


    Shady wants to shrink the log file in their test environment after setting the Recovery Mode to Simple.

    As such shrinking the log file is no problem.

    @Shady

    maybe you need to take a transaction log backup first before calling DBCC SHRINKFILE


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012년 7월 11일 수요일 오후 6:28
  • set the database in SImple recovery mode .then shrink the file (ldf) ,thereafter again set the database in FULL Recovery mode

    @Ramesh

    what is the benefit of switching to Simple Recovery Mode, shrink the log file and switch back to Full Recovery Mode?

    you've to do a transaction log backup before the shrinking anyway and you're loosing the log backup chain and therefore you need to take a fullbackup of the database after switching back to Full Recovery mode.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012년 7월 18일 수요일 오전 11:51
  • Correct about losing the backup chain.  However, a Differential is enough to reestablish the backup chain from the DIFF forward.

    RLF

    2012년 7월 18일 수요일 오후 3:32

모든 응답

  • Size will not be reduced until you use DBCC SHRINKFILE to reduce the size. Generally, runing a shrink operation is not a good idea and may lead to fragmentation.

     


    2012년 7월 11일 수요일 오후 1:26
  • You can shrink the file  : DBCC SHRINKFILE(<Filename>)

    Although shrinking file is not a good option but you can do it if it required.


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    2012년 7월 11일 수요일 오후 2:41
  • You can shrink the file  : DBCC SHRINKFILE(<Filename>)

    Although shrinking file is not a good option but you can do it if it required.


    Shady wants to shrink the log file in their test environment after setting the Recovery Mode to Simple.

    As such shrinking the log file is no problem.

    @Shady

    maybe you need to take a transaction log backup first before calling DBCC SHRINKFILE


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012년 7월 11일 수요일 오후 6:28
  • If you DBCC SHRINKFILE(logfile, size) it will not require rebuilding indexes.   (Shrinking the database files should be followed by rebuilding the indexes.)

    FWIW,
    RLF

    2012년 7월 11일 수요일 오후 7:45
  • Russell my bad.. you are right only datafile requires rebuild indexes. Thanks for the correction.
    • 편집됨 Chirag Shah 2012년 7월 11일 수요일 오후 7:53
    2012년 7월 11일 수요일 오후 7:53
  • Hi,

    I get database back from live to test environemtn. Database recovery model is FULL on live. After restore on Test, I change the recovery model to simple. Issue is the size of log file. How do I reduce the size of log file in test environment?

    Thanks in advance



    Shady

    How often are you going to be performing this copy?  If this is something that will be done on a regular basis - I see no reason to go through the effort of shrinking the log file. 

    If you are going to be running the same processes in test - you probably will need that log file to be the same size anyways.  Generally, the log file in production is the size it is because it needs to be that size to handle those transactions - which can be the processes to rebuild indexes, scheduled data uploads, etc...

    If you really do need to shrink - then use DBCC SHRINKFILE(logicalfile, size) where size is a reasonable size for the environment.


    Jeff Williams

    2012년 7월 14일 토요일 오후 1:48
  • set the database in SImple recovery mode .then shrink the file (ldf) ,thereafter again set the database in FULL Recovery mode

    note?:

    changing the databases from SIMPLE Recovery to FULL Recovery mode changes the lsn Number(log sequence number) ,so think before you change the recovery modes


    Ramesh Babu Vavilla MCTS,MSBI

    2012년 7월 17일 화요일 오전 5:44
  • set the database in SImple recovery mode .then shrink the file (ldf) ,thereafter again set the database in FULL Recovery mode

    @Ramesh

    what is the benefit of switching to Simple Recovery Mode, shrink the log file and switch back to Full Recovery Mode?

    you've to do a transaction log backup before the shrinking anyway and you're loosing the log backup chain and therefore you need to take a fullbackup of the database after switching back to Full Recovery mode.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012년 7월 18일 수요일 오전 11:51
  • Correct about losing the backup chain.  However, a Differential is enough to reestablish the backup chain from the DIFF forward.

    RLF

    2012년 7월 18일 수요일 오후 3:32