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

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?

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.

StackExchange Database Administrators Q#107792, answer score: 7