November 7, 2011 at 1:48 pm
I have a SQL 2005 database in fill recovery mode where tran logs are backed up hourly. The initial size (MB) for the log file is set for 64626.
There is no need to set the initial size so large. It was set when application developers were running efficient code by the previous dba.
I have found plenty of references to reduce the size of the log file once it has grown to a very large size but I can't find and references to change the initial size of the log file.
It is simply a matter of running
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
then DBCC SHRINKFILE (<logfilename>, X) where X will be the initial size of the log file?
November 7, 2011 at 1:54 pm
there is no reset of the initial size, Just use the dbcc shrinkfile to set the log to the size you want it. It will stay that size unless it needs to grow.
DO NOT run the backup log truncate only, it will break your log chain, Just run a normal log backup (your scheduled backup preferably)
---------------------------------------------------------------------
November 7, 2011 at 2:29 pm
You can set the initial size of the data and log files in the CREATE DATABASE statement:
CREATE DATABASE Sales
ON
( NAME = Sales_data,
FILENAME = 'D:\MSSQL\Data\Sales_data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'D:\MSSQL\Data\Sales_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
);
November 7, 2011 at 2:45 pm
The database has already been created and the previous dba bumped up the initial size of the log file to over 60GB.
Since we don't need such a large log file I was trying to determine how to set the initial size to 50MB and to shrink it down.
November 7, 2011 at 3:07 pm
Except for tempdb 'initial' size is meaningless once the database has been created.
What size is the data file, 50MB sounds quite small for the log, you don't want it needing to grow again.
find out the largest log backup you get in a normal processing cycle and set the log big enough to handle that.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply