Manually set log file size after shrink SQL Server 2008 R2

Question

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

Answer 1

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.

Answer 2

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

Answer 3

In complement of Aaron's answer about simple mode, you could schedule 2 (or more) differential backups per day, that way reducing the data loss window of your database operation while retaining SIMPLE mode.

Answer 4

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.