Script to find the orphaned files in a server

Question

Hi,

Does anyone know any script to get all mdf, ndf and ldf files in a server which do not belong to any database?

Answer 1

If you are talking about doing this all from SQL this would require a clr stored procedure or function, since you would need to access the file system.  It is possible, but may be easier to attack from another angle.  What are your requirements?  Do the results need to be maintained in SQL server or returned as a result set with in SQL?  What action(s), if any will you need to take based on the files located?

---

David Dye

Answer 2

Hi,

ACtually, my server drives are running out of space. So i just wanted to find all these orphaned files and delete them. Since it is just to find their names and location, the output can be in anyway.

Answer 3

This can most easily be done in code.  Would you mind me reposting this in the SQL .NET forum?  I can provide the VB code to accomplish this, it may take me just a bit.

---

David Dye

Answer 4

Sure.

Answer 5

Hi David,

Any updates regarding the script?

 

Answer 6

Still workin on the code.  The direction I have headed is to:
1. Populate a datatable with all database names in a specified instance of sql
   a. this uses a data adpater that passes a SELECT statement against the cataolg view sys.databases
2. Use a while loop against the data table to SELECT all file locations of databases in an instance
  a. this uses a data adapter to insert all file paths from databases into antoher data table from the catalog view
      sys.database_files
  b sys.database_files will only return the files of the database that you are querying so this requires dynamically
     building the query using the data table with all the database names
3. Populate a data table with all files with the extension .mdf, .ldf, .ndf from a specified drive

This is as far as I have gotten.  I was considering on using the Datatable.Select method along with a loop to iterate throught the database files table to get all files that are not listed in the data table populated with the sys.database_files.  The code is listed below. 

    Private Sub OrphanedDB()

        'Table used to hold the databases that are in the specific instance
        Dim databases As New DataTable

        'Sub that populates all of the database names in the instance
        PopulateDatabaseTable(databases)

        'Table used to hold the database names and file locations
        Dim files As New DataTable
        Dim cnt As Integer = databases.Rows.Count - 1

        'Sub that populates the table that contains all the database files
        PopulateFilesTable(files, databases, cnt)

        'Drop the databases table as it is no longer needed
        databases.Dispose()

        'Create new table to hold all files associates with a database
        Dim dbFiles As New DataTable
        Dim fllocation As New DataColumn("fileLocation")
        fllocation.DataType = System.Type.GetType("System.String")
        dbFiles.Columns.Add(fllocation)

        PopulateDBFilesTable(dbFiles)

        'ToDo: Use the Select method within a loop to iterate through the databases
        'dbfiles datatable and locate files that are not contained in the databases
        'datatable

    End Sub

    Private Sub PopulateDatabaseTable(ByVal databases As DataTable)

        'Connection string used for both commands to retrieve databases and files
        Dim cn As New SqlConnection("Data Source=localhost;integrated security=sspi;initial Catalog=Northwind;")
        Dim dbcmd As New SqlCommand("SELECT name FROM sys.databases", cn)
        Dim dbda As SqlDataAdapter

        Try
            cn.Open()
            dbda = New SqlDataAdapter(dbcmd)
            dbda.Fill(databases)
        Catch ex As Exception

        End Try

    End Sub

    Private Sub PopulateFilesTable(ByVal files As DataTable, ByVal databases As DataTable, _
    ByVal cnt As Integer)

        'Varaible that will be used to change the db context
        Dim dbname As String

        While cnt >= 0

            dbname = databases.Rows(cnt).Item(0).ToString

            Dim cn As New SqlConnection("Data Source=localhost;integrated security=sspi;initial Catalog=Northwind;")
            Dim cmd As New SqlCommand("USE " & dbname & " SELECT DB_NAME(), physical_name " & _
     "FROM sys.database_files", cn)
            Dim da As SqlDataAdapter

            Try
                da = New SqlDataAdapter(cmd)
                da.Fill(files)

            Catch ex As Exception

            End Try

            cmd.Dispose()
            cnt -= 1

        End While

    End Sub

    Private Sub PopulateDBFilesTable(ByVal dbFiles As DataTable)
        'Create your objects   
        'Change the path/file name to fit your needs   
        Dim di As New DirectoryInfo("c:\")
        Dim fl As FileInfo

        Try
            If Directory.Exists("C:\") Then

                'You are passing the file name as a search string so you   
                'will loop through all folders  
                For Each fl In di.GetFiles("*.*", SearchOption.AllDirectories)

                    'If the file extension(s) are .mdf, .ldf, or .ndf then
                    'a new row is created with the full path and file
                    'and added to the dbfiles table
                    If fl.Extension = ".mdf" Or fl.Extension = ".ldf" _
                    Or fl.Extension = ".ndf" Then
                        Dim Row = dbFiles.NewRow()
                        Row.Item(0) = fl.FullName.ToString
                        dbFiles.Rows.Add(Row)

                    End If
                Next
            Else
            End If

        Catch ex As Exception

        End Try

    End Sub

---

David Dye

Answer 7

try this, let me know if you need any clarification!
the dos DIR command is doing all the heavy lifting here, but you need to turn xp_cmdshell on to get this to work
also this code is all pure t-sql, so you don't need .net ! dont know why the thread was moved!!

--- turn xp_cmdshell on
/*
exec sp_configure 'show advanced' ,1
reconfigure
exec sp_configure 'xp_cmdshell' ,1
reconfigure
*/
drop table #os_files
create table #os_files([physical_name] varchar(2000))

--list all .mdf and .ldf files on the c drive
-- you will need to call this again to 
-- populate the #os_files table, if you have
-- db files on other databases eg. d:, e:

INSERT INTO #os_files exec xp_cmdshell 'DIR C:\*.mdf /b /s'
INSERT INTO #os_files exec xp_cmdshell 'DIR C:\*.ldf /b /s'

select os.physical_name  as files_without_database from sys.master_files db right outer join  #os_files os on db.physical_name = os.physical_name 
where db.database_id is null

Answer 8

It appears that I over complicated this.  Please review Nick's proposal.  If xp_cmdshell is not an option for you I will post the rest of the code, but, if allowed, this would be more efficient.

---

David Dye

Answer 9

Hi,

Thank you.. It workrd.. :)

Answer 10

Thanks. This was very helpful. Here's the same script modified to work on SQL 2000 and up...

/*
exec sp_configure 'show advanced' ,1
reconfigure
exec sp_configure 'xp_cmdshell' ,1
reconfigure
*/
if object_id('tempdb.dbo.#os_files') is not null
	drop table #os_files
create table #os_files([filename] varchar(2000))

--list all .mdf and .ldf files on the c drive
-- you will need to call this again to 
-- populate the #os_files table, if you have
-- db files on other databases eg. d:, e:

INSERT INTO #os_files exec xp_cmdshell 'DIR C:\*.mdf /b /s'
INSERT INTO #os_files exec xp_cmdshell 'DIR C:\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select os.filename as orphaned_files 
from #os_files os
left outer join master.dbo.sysaltfiles db on rtrim(db.filename) = os.filename 
where db.dbid is null
order by 1

---

Chuck