Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Am becoming a somewhat involuntary DBA at work at teh moment 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 databse Properties -> Files? I would guess as well that the database would need to be offline for this to occur?

Thanks in advance

share|improve this question
2  
You plan to backup the database once a night, and run a log backup once a night? Maybe you should consider simple recovery model so the log manages itself. –  Aaron Bertrand Feb 21 '13 at 17:07
    
Aaron, I agree with you on a DR recovery point of view but for operational recovery they may still want it as full. Don't forget that even though they are only doing a log backup once a day it still allows for point in time recovery. –  Kenneth Fisher Feb 21 '13 at 17:11
1  
@Kenneth so if you do a full backup at midnight, then a log backup at 12:05, I find that quite spurious. YMMV. –  Aaron Bertrand Feb 21 '13 at 17:29
    
@Aaron again, all operational but unless I'm mistaken you can use the full backup from the previous night, and the log taken at 12:05 and to a recovery to any point in the previous day. Also if they run into a problem it's no big deal to do a log back, restore from the previous night and do a point in time to get back to a few minutes ago. I'm not saying they should keep it as full, just saying there is more involved than the DR point of view. That being said if they are keeping as full they should be doing log backups more frequently than once a day. –  Kenneth Fisher Feb 21 '13 at 17:43
2  
@Kenneth but if you are only backing up the log once a day, this is why it's huge in the first place! If you need to recover to 12:07 AM yesterday, you need to load the entire log for the whole day to recover 2 minutes. Not very useful. –  Aaron Bertrand Feb 21 '13 at 17:45

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks for this. I fully understand all the comments about shifting to simple recovery model. unfortunately this is a decision that I cannot make and needs to be run through several levels of bureaucracy. It is certainly something I will suggest though. –  Tim Alexander Feb 21 '13 at 17:22

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:

share|improve this answer
    
I think you have a syntax error there (or an older/newer version of SQL?) On 2008 R2 it's FILEGROWTH, not GROWTH. –  Benjol Oct 4 '13 at 4:47

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.