Post reply

Delete mdf, ndf and log files on file system

  • July 18, 2016 at 5:51 am

    #320034

    Hi All,

    I had database with ndf files in 5 filegroups, the big tables is partitioned and data is there in these files.

    I dropped the database but mdf, ndf and log files are still there. I don't want these files to save the space on disk. i tried to delete them but i can't, tried to rename so that i can restore to new database but no luck. I don't have backups of this database. is there any way i can delete these files from file system.

  • July 18, 2016 at 6:40 am

    #1890579

    If you can't delete the files, something is using them.

    Are you sure that it's the right files? Try looking at sys.master_files: does any of those files appear here?

    -- Gianluca Sartori

  • July 18, 2016 at 7:08 am

    #1890584

    farooq_inlondon (7/18/2016)

    Hi All,

    I had database with ndf files in 5 filegroups, the big tables is partitioned and data is there in these files.

    I dropped the database but mdf, ndf and log files are still there. I don't want these files to save the space on disk. i tried to delete them but i can't, tried to rename so that i can restore to new database but no luck. I don't have backups of this database. is there any way i can delete these files from file system.

    Further on Gianluca's answer, are those files you are trying to delete listed in the sys.master_files? If so then they are in use by the server and obviously you will know which database they belong to.

    😎

  • July 18, 2016 at 7:11 am

    #1890585

    Hi Gianluca,

    I tried sys.masterfiles and other queries as well to check if those files are getting used but nothing, these files are not getting used for any database on the instance. I was wondering how come drop command allowed me to drop the database if its not deleting from file location. After googling and reading i have to come to know that only way is to stop instance and delete those files which i don't want to do as its prod system.

    Tried creating a new database and attaching these files but those files doesn't appear in the filesystem when trying to attach. Is there any other way i can delete these orphaned mdf, ndf, ldf files to save space on the disk.

    Thanks

  • July 18, 2016 at 7:15 am

    #1890586

    If you drop an offline database the files aren't deleted.

    You don't need to restart the instance. If it is the case that the DB was offline when dropped, you just need to change the file permissions so that you own them (in explorer) and then you can delete them

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2016 at 7:39 am

    #1890591

    when dropping the database it's online.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply