databse file removed, still in metadata

  • 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)

    ---------------------------------------------------------------------

  • Possibly for database recovery. I noticed after a log backup the entries disapear and databases in simple recovery don't retain the entries.

  • Interesting thanks. I'll back the database up (this is a sandpit environment), restore etc, see when the entry goes for me.

    cheers

    george

    ---------------------------------------------------------------------

  • 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!

    ---------------------------------------------------------------------

  • 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