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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a several-GB import into my SQL Server 2005 (Standard Edition) database.

The import is separated into 4 parts, each part in its own transaction.

As you can guess, the transaction log files are quite big. The space on my disk is also limited, but should fit the data. While doing my import the free space on my disk is about 2 gb.

Does it work to shrink the files, while my transactions are in progress? Would it also have an effect?

share|improve this question
up vote 8 down vote accepted

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the truncation occurs basically when you successfully run a BACKUP LOG statement (I'm omitting some details). So, first, figure out what is the recovery model of your target database, see View or Change the Recovery Model of a Database.

For an in depth look, see How to shrink the SQL Server log. The article explains the circular nature of the log and why often simply issuing DBCC SHRINKFILE results in no reduction of the file (read the article to understand why).

share|improve this answer
We found that our backup task had stopped running for some months. This caused the log file to grow to 330GB by the time we corrected the task. We'd like to shrink the file now since 330GB is way more than we need with the regularity of our transaction log backup process. – crush Oct 14 '15 at 16:07

Yes it works to shrink the log file during transactions are in progress.

 USE [mydatabase]
 DBCC SHRINKFILE (N'mydatabase_log' , 0, TRUNCATEONLY)
share|improve this answer

Your Answer


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.