Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: shrink + remove datafile

    Hi,
    i try to shrink and remove one datafile. But i always get following error:

    Server: Msg 5042, Level 16, State 1, Line 1
    The file 'M1Pdata15' cannot be removed because it is not empty.

    ***********************************************
    use M1K
    go
    dbcc shrinkfile (M1Pdata15,emptyfile)
    go
    use master
    go
    Alter database M1K
    remove file M1Pdata15
    go
    ***********************************************

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86)

    After Shrinking the file has 1.94 MB.

    please help.

    thx

    DocHolliday1108

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This isn't the MDF file by any chance is it? I think SQL Server is awfully fidgety about moving the system catalog for a database.

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Hi,

    no it isnīt the mdf-file. We have 18 Files and i want to shrink it to 3 Files.

    Iīve read, thatīs better for performance. Isnt it ?

    DocHolliday1108

  4. #4
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    I guess it depends why you have 18 in the first place. Typically you want to have your files spread across different drives, or LUNS to increase your maximum throughput and reduce disk queuing.

    What are the results of your DBCC, it may have not completed successfully because sometimes it can't remove pages that are in use. Can you kill all spids in your database and try again, maybe that would help ?
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by DocHolliday
    Iīve read, thatīs better for performance. Isnt it ?
    I don't have much on how many files a database should have. Kilka is right that it is nice to have a database spread out accross different physical devices to prevent, or diminish "hot spots" on the disks. What article/book are you referring to?

  6. #6
    Join Date
    Aug 2004
    Posts
    3
    @kilka

    there no spins to kill. DBCC SHRINKFILE has no errors.

    KB324432 does also not help.

    Any idea ?

    DocHolliday1108

Posting Permissions

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