We regulary copy data from our production ERP environment to multiple
test envrionments. The tables in each environment are the same, but the
owners in each environment are not. Therefore we found it easiest to
simply have jobs that copy the data from production to the test
environments by truncating each table then using 'insert into' rather
than by 'backup and restore over'.

The one problem we have is that one table in particular is very large.
Because of this, the copy of that table grows the transaction log to a
huge size. We have to shrinkfile that transaction log after each copy
or we will run out of disk space by the time we get to the 4th
environment.

The odd thing is, when the dbcc_shrinkfile runs, it gives me the
following error:
Cannot shrink log file 2 (DEVELOPMENT_Log) because all logical log files
are in use.

I tried running a checkpoint right before running the shrinkfile, but I
get the same error. Now the really odd part...... It appears the shrink
does in fact work.... Even though I get the error.

Anybody have any idea what's going on here?


Specifics:

SQL2K on Win2k3
Simple recovery model on all test environments
Autoshrink is OFF

Michael Rogers



MS Sql Server LazyDBA home page