SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DB File could not be deleted. (SQL Server 2000)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lraghunahth
Starting Member

Japan
1 Posts

Posted - 02/23/2010 :  03:14:54  Show Profile  Reply with Quote
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

USA
1421 Posts

Posted - 02/23/2010 :  14:02:14  Show Profile  Visit mfemenel's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000