We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:
Error: the file PRADATA4 cannot be removed because it is
not empty.
The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)
2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO
Can anyone help?
ThanksYou are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.
-PatP|||Originally posted by Pat Phelan
You are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.
-PatP
sorry,I write error,the commands are:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)
2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO|||First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.|||Originally posted by Satya
First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.
How to check open transactions?Use sp_who2?|||DBCC OPENTRAN and refer to books online for more information.|||Originally posted by Satya
DBCC OPENTRAN and refer to books online for more information.
Hi Satya:
Yesterday , I restarted our database service,and used DBCC OPENTRAN to check there are no open transactions,but we still cann't remove the PRADATA4 from database.
Thanks|||Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.|||Originally posted by Satya
Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.
Hi Satya:
Thanks for your help,I have used KB 324432 to resolve the problem.
No comments:
Post a Comment