Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

databse file removed, still in metadata Expand / Collapse
Author
Message
Posted Thursday, May 13, 2010 9:55 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
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)



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

Post #921425
Posted Thursday, May 13, 2010 5:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 2,872, Visits: 3,063
Possibly for database recovery. I noticed after a log backup the entries disapear and databases in simple recovery don't retain the entries.
Post #921698
Posted Friday, May 14, 2010 3:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
Interesting thanks. I'll back the database up (this is a sandpit environment), restore etc, see when the entry goes for me.

cheers

george


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

Post #921857
Posted Friday, May 14, 2010 9:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
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!


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

Post #922153
Posted Monday, July 22, 2013 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 10:25 AM
Points: 1, Visits: 16
same happen to me , in both sql2k5 and 2k8 after a while you see the change
Post #1476125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse