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.

I have a copy of my prod database on a development server. I am using it to test a deployment script.

But when I run it, it says that the log file is full.

I checked and it is at 10 GB (Almost the size of the full database).

I don't need this database but for a few more hours (after that it is going to get dropped).

Is there any way to get rid of the log file gracefully? (It seems wrong to just go delete it.)

NOTE:
I have run this:

select log_reuse_wait, log_reuse_wait_desc, name 
from sys.databases
where name = 'ProdCopyDatabase'

And the result is:

log_reuse_wait  log_reuse_wait_desc    name
2               LOG_BACKUP             ProdCopyDatabase
share|improve this question
add comment

1 Answer 1

up vote 14 down vote accepted

You have two options:

1) Run a LOG backup of the database:

BACKUP LOG foo TO DISK='X:\BackupLocation';

2) Set the recovery model to SIMPLE and checkpoint it.

ALTER DATABASE foo SET RECOVERY SIMPLE;
CHECKPOINT;

This is all part of SQL Server's different recovery models. Based on you working on this in DEV, I'd probably go the second option since it sounds like you don't need to worry about point in time recovery.

Note, this will simply clear out the active transactions, not reduce the actual file size. Once this is complete, you can then shrink the log file.

share|improve this answer
1  
And when you regrow it - consider growing it in appropriately sized chunks based on this post by Kimberly Trip. –  Mike Walsh Dec 3 '12 at 22:25
1  
And a big +1 on Mike's note above "Based on you working on this in DEV, I'd probably go the second option since it sounds like you don't need to worry about point in time recovery." If that note wasn't here, I wouldn't have +1'd this answer. The right answer for a full log because of full mode with no backups is always to understand your recovery model and deal with it appropriately. If this were precious data and you needed point in time, the answer would be to schedule regular log backups. Great answer here, though. –  Mike Walsh Dec 3 '12 at 22:26
add comment

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.