Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: cann't remove data file from user database

    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?

    Thanks
    Last edited by zero_mu; 03-16-04 at 01:13.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    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?

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    DBCC OPENTRAN and refer to books online for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Mar 2004
    Posts
    5
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Mar 2004
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •