I have this script that restores a DB, sets recovery=simple, trims some tables & shrinks files:
RESTORE DATABASE [ABC] FROM DISK (....) ALTER DATABASE [ABC] SET RECOVERY simple go exec [ABC]..trim
"trim" stored proc is as follows:
CREATE PROCEDURE trim AS TRUNCATE table A TRUNCATE table B (...) DELETE TOP 1000 FROM C where date < @somedate DELETE TOP 1000 FROM D where date < @somedate (...) DBCC SHRINKFILE (N'ABC_Log' , 0, TRUNCATEONLY) DBCC SHRINKFILE (N'ABC_data' , 0)
Note - each of the "DELETE TOP 1000 FROM D where" is done in a loop until no more rows to delete. This to keep each transaction small.
When I run all of this script manually, step by step all is well - transaction log is kept small, SHRINKFILE truncates the log table to 1KB
When this is executed all as one script, all in one run, this is what happens:
- transaction logs keeps growing (as if recovery=full)
- SHRINKFILE does not truncate the log, long remains 99% empty but huge
Any hints what could be going wrong? Do I need a "GO" placed somewhere?