Manually set log file size after shrink SQL Server 2008 R2

Am becoming a somewhat involuntary DBA at work at themoment and really need some help on something.

We have a 40GB database in Full Recovery Mode, no log backup configured and a huge log file of 84GB. My plan thus far to salvage this situation is to run a full log backup on the database, shrink the log file and instigate a maintenance plan to run a log backup every night with the database backup to help keep it under control.

My problem is I do not want the log file to shrink down to nothing and spend the first morning on Monday constantly growing. I have a rough estimate as to what the file should be (about 20% of the database) and would like to set this from the get-go to ensure as much contiguous space as possible. Is this just a case of changing "Initial Size" under database Properties -> Files? I would guess as well that the database would need to be offline for this to occur?

Thanks in advance

Your file management can be a completely online operation. You have two paths, depending on your need to retain your log information for recovery purposes:

Point in time recovery not needed

• Convert the database to SIMPLE recovery. Execute a checkpoint to write transactions to disk.

• Flatten the log.

• Resize the log to the appropriate size.

I also recommend setting a fixed growth amount and unlimited growth (so as to help manage your log better). Note, fixed growth amount is very much an it depends amount, I'd recommend going with 1-2 GB initially depending on how much growth that log could expect to see. Ideally, your log won't grow much, so this shouldn't have much of an impact. If your log is growing regularly, you might need to revisit your size.

Accomplished using:

ALTER DATABASE [foo] 
SET RECOVERY SIMPLE;

CHECKPOINT;

DBCC SHRINKFILE (foo_log,0);

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

--Optional if you want the database in full recovery mode 
--for point in time recovery going forward
ALTER DATABASE [foo] 
SET RECOVERY FULL;

Point in time recovery needed

The biggest hangup will be that you can not shrink your log file past your currently active VLF segment. To see this, you can use DBCC LOGINFO in the database context. Any segment with a Status=2 is active. To clear active segments, you will need to run a transaction log backup when no transactions are currently active in that segment. Your steps are:

• Run a transaction log backup.

• Shrink your file. (Ideally flatten, but if your database is active this will be hard to do).

• Repeat steps 1 and 2 until your log is an appropriate size, ideally as small as possible.

• Resize the log to the appropriate size.

Accomplished using:

BACKUP LOG [foo] TO DISK='';

DBCC SHRINKFILE (foo_log,0);

--Repeat the above until your log file is small "enough"

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

Some additional resources to understand what's going on here:

-
SQL Server Recovery Models

-
Kimberly Tripp's t-log recommendations

-
Database Checkpoints

StackExchange Database Administrators Q#35182, answer score: 11