databasename_indx.ndf file not referenced but can't delete I have a SQL Server 2000 Express database for a legacy app I'm ditching soon. Until then, I'm at my 2 GB limit.

According to sp_helpfile the database has one .mdf (900 MB) and one log file, but the .ndf (1100 MB) is not referenced. But if I stop the SQL Server and delete the .ndf file, then I can't access the database once I start SQL Server back up, and the following error is logged:


  Data File .\CallAccounting_indx.ndf does not exist - unable to activate.
  Device activation error. The physical file name '.\CallAccounting_indx.ndf' may be incorrect.


I'm in the middle of purging old records to free up pages so I can't recreate the other errors just now.

I have no idea how to proceed here. Am I truly hitting the 2 GB limit, or is there something easy I can do here to clean up?



groupid allocpolicy status  groupname
1       0       16  PRIMARY
2       0       0   INDEXGROUP




fileid  groupid size    maxsize growth  status  perf    name                filename
1       1       59360   -1      10      1081346 0       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
2       0       96      -1      10      1081410 0       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf




status  fileid  name            filename
32770   1   CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf
32834   2   CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf




fileid  groupid size    maxsize growth  status  perf    dbid    name                filename                                                                    createlsn               droplsn
1       1       59360   -1      10      32770   0       5       CallAccounting_dat  C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_dat.mdf     0x00                    0x00
2       0       96          -1  10      32834   0       5       CallAccounting_log  C:\Program Files\Microsoft SQL Server\MSSQL\Log\CallAccounting_log.ldf      0x00000000000000000000  0x00000000000000000000
3       2       50208   -1      10      32770   0       5       CallAccounting_indx C:\Program Files\Microsoft SQL Server\MSSQL\Data\CallAccounting_indx.ndf    0x050000001E0000000100  0x00000000000000000000
The information about files has somehow got out of synch between the master database and the CallAccounting database.

So I suppose there are two possibilities.


The file information was erroneously removed from the system tables in the user database.
The file row should have been removed from the master database but for some reason wasn't (or maybe master was restored from backup at some point which brought back in information about a previously deleted file).


The second one seems much more likely as I'm pretty sure that in the first case you would be encountering a bunch of other errors when queries tried to access data in that file.

First run DBCC CHECKDB and see if that tells you anything useful.

If it doesn't you could use DBCC IND to dump out information about all relevant objects to check that nothing appears to be using that file.

SET NOCOUNT ON;

CREATE TABLE #DBCCIND   (
     PageFID         TINYINT,
     PagePID         INT,
     IAMFID          TINYINT,
     IAMPID          INT,
     ObjectID        INT,
     IndexID         TINYINT,
     PageType        TINYINT,
     IndexLevel      TINYINT,
     NextPageFID     TINYINT,
     NextPagePID     INT,
     PrevPageFID     TINYINT,
     PrevPagePID     INT
  ); 


DECLARE @id varchar(10)

DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR SELECT DISTINCT id
FROM   sysindexes
OPEN @C1;
FETCH NEXT FROM @C1 INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #DBCCIND
    EXEC('DBCC IND (0, ' + @id + ', -1) WITH NO_INFOMSGS')
  FETCH NEXT FROM @C1 INTO @id ;
END

SELECT IAMFID AS FileId
FROM #DBCCIND
UNION 
SELECT NextPageFID
FROM #DBCCIND
UNION 
SELECT PrevPageFID
FROM #DBCCIND

DROP TABLE #DBCCIND


If you do see FileID of 3 returned then something is definitely using it.

If you don't I'd probably try attaching (copies of) just the mdf and ldf to another instance and test to see it all works correctly (with CHECKDB and selecting all rows from all tables).

If all of this works without error then it is possibly OK to backup everything then go ahead and delete that row referencing the file from master..sysaltfiles (will need ad hoc updates to system catalogs enabled).

I, of course, accept no liability if this all goes horribly wrong however!