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 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
add comment

2 Answers

up vote 7 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
add comment

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

 USE [mydatabase]
 GO
 DBCC SHRINKFILE (N'mydatabase_log' , 0, TRUNCATEONLY)
 GO
share|improve this answer
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.