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?


2 Answers 2


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).

  • 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, 2015 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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