May 13, 2010 at 9:55 am
MSSQL 2005 SP3
I have just noticed the following behaviour:
removed a file from a database using dbcc shrinkfile(emptyfile) and alter database remove file.
Operation completed successfully, checking properties of database file is gone, however in master.sys.master_files there is still a row for the file, all that has happened is the state column has been set to 6 and state_desc set to 'offline'.
I would have expected the row to be deleted, anyone know why SQL behaves in this way? I have tried this on two servers with same results so would appear to be by design.
complete picture:
sys.master_files and sysaltfiles both still show the file
sys.database_files in the database in question show the file
sp_helpfile and sysfiles do not show the file (sp_helpfile queries sysfiles)
---------------------------------------------------------------------
May 13, 2010 at 5:45 pm
Possibly for database recovery. I noticed after a log backup the entries disapear and databases in simple recovery don't retain the entries.
May 14, 2010 at 3:20 am
Interesting thanks. I'll back the database up (this is a sandpit environment), restore etc, see when the entry goes for me.
cheers
george
---------------------------------------------------------------------
May 14, 2010 at 9:41 am
Edogg,
I can confirm the entry is removed following a log backup (Full backup does NOT remove it). So the theory it is required should the db be recovered sounds correct.
I learnt something today!
---------------------------------------------------------------------
July 22, 2013 at 10:26 am
same happen to me , in both sql2k5 and 2k8 after a while you see the change
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply