none
SQL Server 2012 LDF reduce RRS feed

  • Question

  • On SQL Server 2012, I have a database in which the MDF size 33 GB, and ldf size 7 GB. Database is used for daily charging cubes. I would reduce the initial value on the LDF 10% -15% of the MDF, autogrowth to 50% of the LDF. Limited value'd left default.
    Can you tell me how can I reduce the initial value of the LDF to the desired value?
    I do not want the database offline or some unforeseen scenarios.
    Monday, October 12, 2015 11:48 AM

Answers

  • You can reduce size of Log file by using Alter database command

    ALTER DATABASE [db_name] 
    SET RECOVERY SIMPLE;
    
    sp_helpdb db_name --get logical log file name
    
    CHECKPOINT;
    
    DBCC SHRINKFILE (db_name_log,0);
    
    ALTER DATABASE [db_name]
    MODIFY FILE (db_name_log,SIZE=240MB,MAXSIZE=UNLIMITED,FILEGROWTH=500MB);--dummy values change accordingly
    
    ALTER DATABASE [db_name] 
    SET RECOVERY FULL;
    

    You can read this article and use queries given to calculate appropriate value for autogrowth size. Proper initial size would pre-alllocate space and would avoid frequent autogrowths which are not so good events.

    Dont keep Autogrowth in percentage, please calculate sensible value by script provided in link and keep that value


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, October 12, 2015 12:07 PM

All replies

  • You can reduce size of Log file by using Alter database command

    ALTER DATABASE [db_name] 
    SET RECOVERY SIMPLE;
    
    sp_helpdb db_name --get logical log file name
    
    CHECKPOINT;
    
    DBCC SHRINKFILE (db_name_log,0);
    
    ALTER DATABASE [db_name]
    MODIFY FILE (db_name_log,SIZE=240MB,MAXSIZE=UNLIMITED,FILEGROWTH=500MB);--dummy values change accordingly
    
    ALTER DATABASE [db_name] 
    SET RECOVERY FULL;
    

    You can read this article and use queries given to calculate appropriate value for autogrowth size. Proper initial size would pre-alllocate space and would avoid frequent autogrowths which are not so good events.

    Dont keep Autogrowth in percentage, please calculate sensible value by script provided in link and keep that value


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, October 12, 2015 12:07 PM
  • LDF is transaction log files. Transaction log files keep increasing if your Recovery mode is Full or Bulk Recovery mode. To reduce the physical size of a log file, you must shrink the log file. First you need make backup. It will not take your DB offline.

    I am suggesting same Shankys script for shrinking log file . Old script was deprecated 


    • Edited by ShabSQL Monday, October 12, 2015 12:50 PM Old Script
    Monday, October 12, 2015 12:08 PM
  • BACKUP LOG <DB> WITH TRUNCATE_ONLY

    The Option TRUNCATE_ONLY is deprecated since Version 2005; see BACKUP (Transact-SQL) => Transaction Log Truncation => Note: "The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued ..."

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 12, 2015 12:22 PM
  • Thanks for correcting me.. 
    Monday, October 12, 2015 12:47 PM
  • LDF is transaction log files. Transaction log files keep increasing if your Recovery mode is Full or Bulk Recovery mode. To reduce the physical size of a log file, you must shrink the log file. First you need make backup. It will not take your DB offline

    USE DB
    GO
    DBCC SHRINKFILE(<LogFileName>, 1)
    BACKUP LOG <DB> WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(<LogFileName>, 1)
    GO

    I suggest dont do this, this is very dangerous advise. You would be dumping your log contents *nowhere* and deleteing it. Plus as Olaf suggested this won't work in 2012 anyways

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, October 12, 2015 12:47 PM
  • On SQL Server 2012, I have a database in which the MDF size 500 MB, with autogrowth 1 MB, maxsize UNLIMITED, and LDF size 40 GB, with autogrowth 10%, maxsize UNLIMITED. Database iz used for update and insert, and database in database mirroring, asynchronous mode. I use:

    DBCC SHRINKFILE (db_name_log,0);
    
    ALTER DATABASE [db_name]
    MODIFY FILE (db_name_log,SIZE=100MB,MAXSIZE=UNLIMITED,FILEGROWTH=10MB);
    

    Thereafter, the LDF has not decreased and remained the same size as before.
    What do I do if I did, so I reduced the LDF?

    Thursday, October 29, 2015 10:11 AM