Manually set log file size after shrink SQL Server 2008 R2

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

1. Convert the database to SIMPLE recovery. Execute a checkpoint to write transactions to disk.
2. Flatten the log.
3. 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:

1. Run a transaction log backup.
2. Shrink your file. (Ideally flatten, but if your database is active this will be hard to do).
3. Repeat steps 1 and 2 until your log is an appropriate size, ideally as small as possible.
4. Resize the log to the appropriate size.

Accomplished using:

BACKUP LOG [foo] TO DISK='<Location of t-log backup>';

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

Just shrink to what you think is the optimal size. Don't use the UI, just do this - let's say 200 MB is your optimal size:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);

If you're only interested in taking a log backup once a day, and aren't interested in point-in-time recovery, then you should switch to simple recovery model. This will mean log backups are unnecessary (in fact impossible) but the contents of the log will self-manage.

If you want log backups to be meaningful, don't plan on taking a full backup at night and then a single log backup right after. This keeps you in full recovery model, makes the log work really hard, and doesn't buy you anything. So if you want point-in-time recovery, run log backups more frequently at a rate that satisfies your data loss tolerance. If you don't ever want to lose more than 15 minutes of data, run a log backup every 15 minutes.

Actually no, the database doesn't need to be offline to shrink the log. And I will say this is probably one of the few cases where shrinking the log is a good idea. You could set the initial size, but it would be easier to do the shrink and tell it to shrink down to a specific size.

USE [DBName]
GO
DBCC SHRINKFILE (N'LogName' , SizeInMg)
GO

You can also do it by using the GUI and using the second radio button and the checkbox that says how big you want the log to be in the end. You can get to the GUI by right clicking on the database in object explorer in SSMS, selecting tasks, shrink, files.