How can I reduce the Initial Size of a LOG file?

Question

I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb. No backups were being performed.

I've implemented full and transaction backups to match our recovery needs, and as a result the transaction file is always ~99.9% empty, never exceeding 30 or 40 mb used. I'd like to reduce the initial size so I can shrink the log down to a more apprpriate size, 200 mb or so.

Does anyone know how I can redefine the Log file with an Init size of 200 mb?

Answer 1

Does anyone know how I can redefine the Log file with an Init size of 200 mb?

You have to use ALTER DATABASE [YOUR_DB_NAME] ... MODIFY FILE ... SIZE

ALTER DATABASE [your_db_name] 
SET RECOVERY SIMPLE;

CHECKPOINT;

-- shrink the log file to 200MB
DBCC SHRINKFILE (your_db_name_log,200);

--- define the initial size to 200MB as well.. CHANGE it as per your needs!
ALTER DATABASE [your_db_name]
MODIFY FILE (NAME=your_db_name_LOGICAL_log,SIZE=200MB,MAXSIZE=UNLIMITED,FILEGROWTH=100MB);

--Optional if you want the database in full recovery mode 
--for point in time recovery going forward
ALTER DATABASE [your_db_name] 
SET RECOVERY FULL;
--- Take full backup to establish log chain
-- take log backups to help truncate the log file and keep it manageable

Suggestions:

• Initial size of 200MB is very less. Depending on how busy your system is and how frequent you take log backups (if your db is in full recovery), I would suggest you to look into making an initial size and autogrowth more sensible.
• Its always a good idea to enable Instant file initialization to help for data file autogrowth events (as well as database creation and restoring database). Refer Database defragmentation and autogrowth settings answer for more details.

Answer 2

First of all please note initial size is not actually what it means. Yes you can do it but make sure you don't use SSMS GUI.

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 (name=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

Answer 3

Brent,

Your approach is going to depend on what your recovery mode the database is in.

This is the MSDN DBCC SHRINKFILE reference for your version of SQL Server.

For databases that are in SIMPLE recovery mode you should be able to execute the

    DBCC SHRINKFILE (N'<logical_file_name_of_the_log>'<Size In MB>)          

That should be all that is required and the intial size should get reset to what you just shrunk the file to.

For databases that are in FULL recovery mode you have two options because the shrink command will not shrink the file if there are transactions that haven't been marked as backed up.

The first is to do a backup of the transaction log which will mark all of the items that get backed up as reusable. After that you can run the shrink command.

The second is to change the recovery mode to SIMPLE then execute the shrink command and once that is finished change the recovery mode back to FULL. (With this approach you will only be able to recover to your most recent full backup plus differential). You can find the details of how to do this in the MSDN link provided.

Both options will also reset the initial size of the file to what you just shrunk it to.

Once you have the log file shrunk you will need to setup regular transaction log backups to keep it from growing again.

In all recovery modes and options on shrinking the transaction log be prepared for any users of the database to encounter errors while the file is being shrunk. SQL Server could also fail your command if it's too busy servicing requests. If at all possible schedule some down time and put the database in single user mode using the commands

    use <database>
    Go
    alter database <database> set single_user with rollback immediate 
    Go
    DBCC SHRINKFILE (N'<logical_file_name_of_the_log>'<Size In MB>)
    Go
    alter database <database> set multi_user with rollback immediate
    Go

This will allow you to get the shrink done without other statements blocking you.