cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

I followed the note 363018 and before deleting the file, I used the SHRINK 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)

R/3 Enterprise4.7 Extension1.1

Kernel Patch WAS640 247

BASIS SP Level: SAP_BASIS 620 0047 SAPKB62047

Accepted Solutions (1)

Accepted Solutions (1)

former_member524429
Active Contributor
0 Kudos

Hi,

DBCC SHRINKFILE('ABCDATA2',EMPTYFILE)

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

ABCDATA2 is a primary File ? The primary data file (.mdf) cannot be removed.

DBCC SHRINKFILE('ABCDATA2',EMPTYFILE) command will Migrates all data from the specified file ABCDATA2 to other files in the same filegroup. In that way it will empty that ABCDATA2 File.

So ensure that you are having enough Disk space.

Also use DBCC showfilestats command to check if your database file still has some used extents.

Regards,

Bhavik G. Shroff

Former Member
0 Kudos

Hi,

Thanks for your reply.

>>ABCDATA2 is a primary File ? The primary data file (.mdf) cannot be removed.

The file is a .ndf file.

>>DBCC SHRINKFILE('ABCDATA2',EMPTYFILE) command will Migrates all data >>from the specified file ABCDATA2 to other files in the same filegroup. In >>that way it will empty that ABCDATA2 File.

>>So ensure that you are having enough Disk space.

I had checked the disk space and the drive has enough space.

I will shortly check the extents and get back to you.

Meanwhile can you think of any other issues that could be the reason for this error. ?

Best Regards

Raghunahth L

former_member524429
Active Contributor
0 Kudos

Hi

Size of that Datafile ? The execution of DBCC SHRINKFILE('ABCDATA2',EMPTYFILE) command was fast ?

Have you tried to re-run DBCC SHRINKFILE('ABCDATA2',EMPTYFILE) again and again and have checked the space of that Data file ?

Regards,

Bhavik G. Shroff

Former Member
0 Kudos

Hi,

Result of Trcd. DB02 :

-


FileName Size(MB) Used(MB) Limit Growth Physical file name of the database file Free disk Filegroup

-


ABCDATA1 112,000 105552 No limit 60 MB E:\ABCDATA1\ABCDATA1.mdf 41,756 PRIMARY

ABCDATA2 114,733 0 No limit 60 MB O:\ABCDATA2\ABCDATA2.ndf 90,322 PRIMARY

ABCDATA3 120,000 111024 No limit 60 MB P:\ABCDATA3\ABCDATA3.ndf 33,756 PRIMARY

ABCDATA4 120,000 107088 No limit 60 MB W:\ABCDATA4\ABCDATA4_Data.NDF 33,756 PRIMARY

ABCDATA5 123,000 107168 No limit 60 MB B:\ABCDATA5\ABCDATA5.ndf 30,756 PRIMARY

ABCDATA6 123,000 107184 No limit 60 MB G:\ABCDATA6\ABCDATA6.ndf 30,756 PRIMARY

ABCDATA7 86,000 78792 No limit 60 MB H:\ABCDATA7\ABCDATA7.ndf 63,854 PRIMARY

ABCDATA8 85,000 78160 No limit 60 MB K:\ABCDATA8\ABCDATA8.ndf 68,264 PRIMARY

ABCLOG1 16,261 N/A No limit 10% F:\ABCLOG1\ABCLOG1.ldf 24,768

Result of

DBCC showfilestats;

-


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.

Best Regards

Raghunahth L

Edited by: L Raghunahth on Feb 23, 2010 4:04 PM

Former Member
0 Kudos

Hi,

Request you to look the above DB02 and DBCC SHOWFILESTATS results and please let me know if everything is ok.

Thanks & Best Regards

Raghunahth L

ken_halvorsen2
Active Participant
0 Kudos

Hi Raghunahth L

I've found that if this file was one of the 1st files initially installed (i.e. sapdata1.mdf sapadat2.ndf, sapdata3.ndf), you may not be able to delete it.

Are you trying to delete the entire database or just shrink it?

If you're just trying to schrink it and want to delete some of the datafiles, I would suggest to shrink and delete some of the later files that were added after the intial installation.

Ken

Former Member
0 Kudos

Hi Ken,

Thanks for your reply.

> Are you trying to delete the entire database or just shrink it?

> If you're just trying to schrink it and want to delete some of the datafiles, I would suggest to shrink and delete

> some of the later files that were added after the intial installation.

I would like to delete some of the datafiles so that the physical drive can be separated and used for the Production Server.

I would consider your suggestion as well. Thanks a lot.

> I've found that if this file was one of the 1st files initially installed (i.e. sapdata1.mdf sapadat2.ndf,

> sapdata3.ndf), >> you may not be able to delete it.

Could you give more details as to whether you had experienced the above issue yourself ?

Thanks & Best Regards

Raghunahth L

Former Member
0 Kudos

Hi

http://www.sqlservercentral.com/blogs/jeffrey_yao/archive/2009/08/11/error-5042-cannot-remove-a-file...

This article discusses of shrinking the file before removing it. Hope it helps.

Former Member
0 Kudos

Hi

Thanks for the reply.

>SELECT * FROM sys.allocation_units a

>INNER JOIN sys.filegroups fg

>ON fg.data_space_id = a.data_space_id

>AND fg.name = 'fg_data_20090801';

As suggested by you, I am planning to use the above queryto find out whether the datafile is empty or not. But sy.allocation_units could not be found in SQL Server 2000. Could you know the alternative in SQL Server 2000.

Thanks & Best Regards

Raghunahth L

ken_halvorsen2
Active Participant
0 Kudos

Hi Raghunahth

Yes, I did much the same thing in a system recently. 1st, I deleted production data from a restored system, then I shrank 5 of 8 datafiles and deleted them.

I tried shrinking the first 3 datafiles, because there was alot of freespace, but they would only shrink to the original installation size.

I'm assuming you are having the same problem becuase you mentioned datafile3. The other 5 datafiles that were added after the initial installation were easily shrank and deleted.

Ken

Former Member
0 Kudos

Hi Ken,

Thanks a lot for your reply. I consider your inputs very valuable as you had actually faced it and found a solution yourself.

BTW, I am yet to execute ( as per your advise ) and see the results for myself as our next maintenance schedule is during end of this month.

Out of curiosity, I would like to ask a question :

You mean to say its a bug or something ?. Or it is a kind of rule that you cannot delete the datafiles (1,2 and 3) created during Installation of SAP.

According to the SAP Note 363018, it just says that

"The primary data file (.mdf) cannot be removed."

Actually I am unable to find any SAP documents (notes / SAP Help) or even MSDN documents which says that you cannot remove the datafiles created during installation of SAP. By the way, did you come across any ?

Thanks & Best Regards

Raghunahth L

PS: Also invite input from others in the forum, if anyone who had actually been able to successfully delete the Datafile2 and Datafile3 which are originally got created during installation of SAP.

I mean using the shrinkfile emptyfile and alter database commands:

USE ABC

GO

DBCC SHRINKFILE ('ABCDATA2', EMPTYFILE)

GO

ALTER DATABASE ABC

REMOVE FILE ABCDATA2;

GO

Answers (2)

Answers (2)

Former Member
0 Kudos

The problem has been solved alternatively by moving the data file to a separate physical drive.

Former Member
0 Kudos

I am sending the Error log:

-


2010-02-06 19:22:41.97 server Microsoft SQL Server 2000 - 8.00.919 (Intel X86)

Mar 1 2004 15:15:50

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: )

2010-02-13 11:30:33.24 spid357 DBCC CHECKDB (ABC) executed by ABC found 0 errors and repaired 0 errors.

2010-02-13 13:35:35.23 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 14:40:12.65 backup BACKUP failed to complete the command backup log ABC to disk='N:\sqllog\sqllog_20100213144012.bak';

2010-02-13 14:55:59.47 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 15:04:34.98 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 15:26:35.00 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 16:06:54.25 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 16:07:19.67 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 16:37:24.46 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 16:37:24.48 backup BACKUP failed to complete the command USE ABC

DBCC TRACEON(8901)

backup log ABC

DBCC SHRINKFILE('ABCDATA2',EMPTYFILE)

DBCC TRACEOFF(8901)

2010-02-13 16:37:50.18 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 16:55:09.53 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 16:55:09.53 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 16:55:22.17 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 16:55:47.53 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 17:07:28.66 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 17:08:03.94 spid55 DBCC TRACEON 8901, server process ID (SPID) 55.

2010-02-13 17:08:29.08 spid55 DBCC TRACEOFF 8901, server process ID (SPID) 55.

2010-02-13 19:44:58.20 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112348:20272:1, last LSN: 112348:22935:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'H:\sqllog\sqllog_20100213194457.bak'}).

2010-02-13 19:44:58.45 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112348:22935:1, last LSN: 112348:22935:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {'H:\sqllog\sqllog_20100213194457.bak'}).

2010-02-13 20:35:19.67 backup Database backed up: Database: master, creation date(time): 2010/02/06(19:22:45), pages dumped: 2116, first LSN: 362:238:1, last LSN: 362:240:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\master_bkup_.bak'}).

2010-02-13 20:35:20.72 backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52), pages dumped: 93, first LSN: 20:334:1, last LSN: 20:336:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\model_20100213203518.bak'}).

2010-02-13 20:35:22.09 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 3395, first LSN: 395:96:1, last LSN: 395:99:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\msdb_20100213203518.bak'}).

2010-02-13 20:36:01.62 spid161 Database ABC: IO is frozen for snapshot

2010-02-13 20:36:13.62 spid161 Database ABC: IO is thawed

2010-02-13 20:36:13.67 backup Database backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), pages dumped: 1, first LSN: 112348:32919:1, last LSN: 112348:33347:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE, MEDIANAME='SQL Snapshot Disk Media': {'IBMcs0VD'}).

2010-02-13 20:36:13.73 spid160 Error: 100007, Severity: 10, State: 7

2010-02-13 20:36:13.73 spid160 The backup data was stored in the following drive(s).

>>> 'E:' (for Database 'ABC')

.

2010-02-14 10:40:25.79 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112348:22935:1, last LSN: 112349:176776:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100214104015.bak'}).

2010-02-14 11:21:04.38 spid173 DBCC TRACEON 8901, server process ID (SPID) 173.

2010-02-14 11:34:47.26 spid173 DBCC TRACEOFF 8901, server process ID (SPID) 173.

2010-02-14 14:40:18.70 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112349:176776:1, last LSN: 112349:194068:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100214144017.bak'}).

2010-02-14 18:00:13.93 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112349:194068:1, last LSN: 112349:213369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'H:\sqllog\sqllog_20100214180012.bak'}).

2010-02-14 18:40:13.16 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112349:213369:1, last LSN: 112349:216557:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100214184012.bak'}).

2010-02-14 20:35:17.03 backup Database backed up: Database: master, creation date(time): 2010/02/06(19:22:45), pages dumped: 2116, first LSN: 362:246:1, last LSN: 362:248:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\master_bkup_.bak'}).

2010-02-14 20:35:18.05 backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52), pages dumped: 93, first LSN: 20:339:1, last LSN: 20:341:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\model_20100214203515.bak'}).

2010-02-14 20:35:19.56 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 3395, first LSN: 395:177:1, last LSN: 395:180:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqldata\msdb_20100214203515.bak'}).

2010-02-14 20:35:54.70 spid177 Database ABC: IO is frozen for snapshot

2010-02-14 20:36:06.58 spid177 Database ABC: IO is thawed

2010-02-14 20:36:06.69 backup Database backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), pages dumped: 1, first LSN: 112349:222929:1, last LSN: 112349:223110:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE, MEDIANAME='SQL Snapshot Disk Media': {'IBMcs0VD'}).

2010-02-14 20:36:06.75 spid176 Error: 100007, Severity: 10, State: 7

2010-02-14 20:36:06.75 spid176 The backup data was stored in the following drive(s).

>>> 'E:' (for Database 'ABC')

.

2010-02-14 22:40:25.91 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112349:216557:1, last LSN: 112350:167575:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100214224018.bak'}).

2010-02-15 02:40:16.03 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112350:167575:1, last LSN: 112350:248361:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100215024013.bak'}).

2010-02-15 06:40:21.73 backup Log backed up: Database: ABC, creation date(time): 2010/01/24(21:07:05), first LSN: 112350:248361:1, last LSN: 112351:45114:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'N:\sqllog\sqllog_20100215064019.bak'}).