DB File could not be deleted. (SQL Server 2000)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DB File could not be deleted. (SQL Server 2000)

Author

Topic

lraghunahth
Starting Member

1 Post

Posted - 2010-02-23 : 03:14:54

Hi,

My Objective is to delete a DB File from SQL Server 2000.

Database Name : ABC
Total No of DB Files : 8
File to be deleted : ABCDATA2.ndf

I used the DBCC SHRINKFILE command EMPTYFILE option to empty the contents.

It appeared that the operation successfully completed. I could confirm the message. After that when I executed the alter database command to remove the above said DB file, it is throwing the following error and the command fails :

The file 'ABCDATA2' cannot be removed because it is not empty

When I checked for the usage of the above said DB File, it is showing as 1.8MB

Before I shrinked the above file, the usage was around 88 GB. From this I could understand that almost all the contents have been moved to the rest of the DB files.

I had used the following commands:

use ABC
DBCC TRACEON(8901)
DBCC SHRINKFILE('ABCDATA2',EMPTYFILE)
ALTER DATABASE ABC REMOVE FILE ABCDATA2
DBCC TRACEOFF(8901)

(Error Message)
The file 'ABCDATA2' cannot be removed because it is not empty

Would like to know if you had faced the similiar issue and the solution you had applied it to rectify it.

Best Regards
Raghunahth L

General System Information

RAM 4GB
CPU Xeon 2.7Ghz
SQLServer Version : SQLServer2000 3a hotfix 8.00.919
OS WindowsServer2003 32Bit (No SP)

FileID FileGroup Total Extents Used Extents Name FileName

1 1 1792000 1688781 ABCDATA1 E:\ABCDATA1\ABCDATA1.mdf
3 1 1835739 30 ABCDATA2 O:\ABCDATA2\ABCDATA2.ndf
4 1 1920000 1776416 ABCDATA3 P:\ABCDATA3\ABCDATA3.ndf
6 1 1920000 1713409 ABCDATA4 W:\ABCDATA4\ABCDATA4_Data.NDF
7 1 1968000 1714709 ABCDATA5 B:\ABCDATA5\ABCDATA5.ndf
8 1 1968000 1714960 ABCDATA6 G:\ABCDATA6\ABCDATA6.ndf
9 1 1376000 1260704 ABCDATA7 H:\ABCDATA7\ABCDATA7.ndf
10 1 1360000 1250530 ABCDATA8 K:\ABCDATA8\ABCDATA8.ndf

Info : I had even restarted the DB and rerun the Shrink command. But no effect.

mfemenel
Professor Frink

1421 Posts

Posted - 2010-02-23 : 14:02:14

Do you have any tables created on that file group or indexes?
Select s.groupname,i.id,object_name(i.id)
From sysfilegroups s
INNER JOIN sysindexes i
ON i.groupid = s.groupid
ORDER BY object_name(i.id)

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources