on 02-23-2010 4:46 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
Hi
This article discusses of shrinking the file before removing it. Hope it helps.
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
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
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
The problem has been solved alternatively by moving the data file to a separate physical drive.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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'}).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.