Just to be clear, when I say 'owner' I actually mean that the tables
were added with different schemas in each db. In prod it would be
PROD.PRDDTA.F0911 but in DEV it would be DEV.DEVDTA.F0911.

Also, all recovery models in test environments are on simple.

I may have to go to 'backup and restore' soon anyhow as they are finally
allowing me to move the production database to a different server.

Michael

-----Original Message-----
From: Hugh du Toit
[mailto:mssqldba-ezmlmshield-x8356665.[Email address protected]
Sent: Wednesday, March 30, 2005 8:52 AM
To: LazyDBA Discussion
Subject: RE: Errors on shrinkfile


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]




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