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?
Best Answer
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).