locked
To find Unused detached files RRS feed

  • Question

  • How to find unused detached files (mdf,ndf,ldf) in sql server instance in standalone and cluster environment? Files which were detached by someone and are residing on disk and taking place. I want to find out those files.

    Thanks

    Monday, January 31, 2011 11:17 PM

Answers

  • you can manually search in file-system *.mdf and delete which is not in used. the files which are attached with SQL, can not be deleted.

     


    --------------------------------- Devender Bijania

    Except that this doesn't apply to SQL Express and user instances where the database file is attached by the application connectionstring to the instance of SQL for use by the application.  Even if it was an Enterprise Edition installation of SQL where the database files are locked when they are actively open by SQL Server, I would caution against deleting any database files that you don't have backed up to some form of stable media like tape, or multiple DVD's where you can recover said file from your backups.  This kind of thing can be a career ending event for a DBA.  I make sure that I have the physical tape in my own possession and that I have validated that I can restore the database files from the backup on that tape anytime I delete the last physical copy of that I have on one of my servers.  In seven years working with SQL Server, I have only had to ever go back to one of these tapes twice, but the fact that I had a copy of the data that was in that database proved to be worth the additional steps and space required to store those tapes separately with appropriate labeling in my file cabinet.  When I left my last job I provided the new DBA with a all of these types of tapes and DVD's along with a Word Document explaining what they were, just in case they needed them.
    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by DBA_LEARNER Thursday, February 3, 2011 5:01 PM
    Tuesday, February 1, 2011 6:32 AM

All replies

  • You can try using backup views if you store backup history. For example: msdb.dbo.backupfile
    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Monday, January 31, 2011 11:37 PM
  • Query sys.master_files and document the path and location of all the database files active in the instance.  The compare that to the mdf/ndf/log files that you find in the OS file system.  I would recommend against deleting any files and that you copy them to another location and that you write them off to tape before deleting anything.  You never know what might come back to get you.
    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by WeiLin Qiao Thursday, February 3, 2011 6:47 AM
    Tuesday, February 1, 2011 1:06 AM
  • you can manually search in file-system *.mdf and delete which is not in used. the files which are attached with SQL, can not be deleted.

     


    --------------------------------- Devender Bijania
    Tuesday, February 1, 2011 5:29 AM
  • you can manually search in file-system *.mdf and delete which is not in used. the files which are attached with SQL, can not be deleted.

     


    --------------------------------- Devender Bijania

    Except that this doesn't apply to SQL Express and user instances where the database file is attached by the application connectionstring to the instance of SQL for use by the application.  Even if it was an Enterprise Edition installation of SQL where the database files are locked when they are actively open by SQL Server, I would caution against deleting any database files that you don't have backed up to some form of stable media like tape, or multiple DVD's where you can recover said file from your backups.  This kind of thing can be a career ending event for a DBA.  I make sure that I have the physical tape in my own possession and that I have validated that I can restore the database files from the backup on that tape anytime I delete the last physical copy of that I have on one of my servers.  In seven years working with SQL Server, I have only had to ever go back to one of these tapes twice, but the fact that I had a copy of the data that was in that database proved to be worth the additional steps and space required to store those tapes separately with appropriate labeling in my file cabinet.  When I left my last job I provided the new DBA with a all of these types of tapes and DVD's along with a Word Document explaining what they were, just in case they needed them.
    Jonathan Kehayias | Senior Database Administrator and Consultant
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by DBA_LEARNER Thursday, February 3, 2011 5:01 PM
    Tuesday, February 1, 2011 6:32 AM
  • Thanks for your help.
    Thursday, February 3, 2011 5:01 PM
  • I use the script in the below link all the time and is pretty helpful, I have modified it not to include .bak files.

     

    http://sqlserverpedia.com/wiki/Find_Detached_Databases

    Monday, February 7, 2011 10:02 PM