If these are test environments, then set those databases' recovery model to 'simple' (are they on 'full' at the moment?)... it will cause your log files to not grow that big.

Or, use the backup+restore strategy again, but run a script afterwards to loop through the tables and set the owner of each object to that of dbo or whatever you want it to be - if you want all the tables to have one specific owner...



-----Original Message-----
From: Rogers Michael L [mailto:mssqldba-ezmlmshield-x64824515.[Email address protected]
Sent: Wednesday, March 30, 2005 16:46 PM
To: LazyDBA Discussion
Subject: Errors on shrinkfile

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




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



MS Sql Server LazyDBA home page