Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Like I said, legacy app. It's going away in 4 months when we get a new phone system. Until then, I make it work. –  longneck Jan 3 at 22:05
4  
You just deleted the .ndf file after stopping the service? I hope you took a backup of that file. –  Aaron Bertrand Jan 3 at 22:17
3  
I think I speak for all of dba.se when I say I would recommend you not delete those files. –  Zane Jan 3 at 22:26
1  
Seriously? Rtfm? I get it, sql 2000 is old. Stop badgering me for trying to make the best of a real world situation. Why is the database dependent on a file that is not listed by sp_filehelp? –  longneck Jan 3 at 22:47
1  
@MartinSmith: I think the issue is about whether the .ndf can be deleted/truncated/whatever without resorting to purging old data, i.e. whether it actually contains data or not. –  Andriy M Jan 3 at 22:48

1 Answer 1

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.

  1. The file information was erroneously removed from the system tables in the user database.
  2. 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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.