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?
sys.databases
, what doeslog_reuse_wait_desc
show? You may need to add aCHECKPOINT
into the mix.