We have a 2008R2 production DB in full recovery mode.
To produce a minimised dev version, for SQL Server 2014, the following steps are run:
RESTORE DATABASE BigDB FROM DISK = N'C:\Share\BigDB.bak' WITH FILE = 1,
MOVE N'BigDB_Data' TO N'C:\Database\BigDB_Data',
MOVE N'BigDB_Audit]' TO N'C:\Database\BigDB_Audit',
MOVE N'BigDB_Log' TO N'C:\Database\BigDB_log.LDF', NOUNLOAD, REPLACE, STATS = 5
alter database BigDB set recovery simple with NO_WAIT
alter database BigDB set compatibility_level = 120
go
-- {truncate / delete statements.. with no additional 'go'}
DBCC SHRINKFILE (N'[BigDB_Audit]' , 500)
DBCC SHRINKFILE (N'BigDB_Data' , 1000)
DBCC SHRINKFILE (N'BigDB_Log' , 1000)
DBCC SHRINKFILE (N'BigDB_Data' , 1000) -- Without this repeat, file remains same
DBCC SHRINKFILE (N'BigDB_Log' , 1000) -- Without this repeat, file remains same
I've observed that without the repeat of the shrinkfile on the main data file, and the log, one or both does not actually shrink.
Can this behaviour be expected, and if so why?
CHECKPOINT
. – Erik Darling Oct 12 '17 at 12:20