SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


databse file removed, still in metadata


databse file removed, still in metadata

Author
Message
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
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)

---------------------------------------------------------------------
JeremyE
JeremyE
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5985 Visits: 4088
Possibly for database recovery. I noticed after a log backup the entries disapear and databases in simple recovery don't retain the entries.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
Interesting thanks. I'll back the database up (this is a sandpit environment), restore etc, see when the entry goes for me.

cheers

george

---------------------------------------------------------------------
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
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!

---------------------------------------------------------------------
matias lapera
matias  lapera
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 16
same happen to me , in both sql2k5 and 2k8 after a while you see the change
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search