0

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?

8
  • 2
    If you query sys.databases, what does log_reuse_wait_desc show? You may need to add a CHECKPOINT into the mix. Jul 9 '21 at 17:21
  • it is set to "NOTHING" Jul 9 '21 at 17:23
  • 1
    You can't have GO since it is a stored proc, and even if you make int a non proc, GO shouldn't matter. . I'd throw in a few WAITFOR commands and play with the delay. Jul 9 '21 at 18:29
  • hmm... thanks for the hint, will try. any ideas where to put the wait for? I am really puzzled by this ... should I do wait for after setting the DB to simple recovery ? Jul 9 '21 at 19:26
  • 2
    Yep, that is what my thought was. Give slq server time to breathe between each delete. You can also throw in a CHECKPOINT command after the pause. Jul 10 '21 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.