20 สิงหาคม 2552 10:54Hi,
Does anyone know any script to get all mdf, ndf and ldf files in a server which do not belong to any database?
- ย้ายโดย David DyeModerator 20 สิงหาคม 2552 12:08 Post requires using .NET (From:Transact-SQL)
20 สิงหาคม 2552 11:14ผู้ดูแล
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?
20 สิงหาคม 2552 11:25Hi,
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.
20 สิงหาคม 2552 11:28ผู้ดูแล
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.
20 สิงหาคม 2552 11:41
21 สิงหาคม 2552 5:09
Any updates regarding the script?
21 สิงหาคม 2552 10:52ผู้ดูแล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
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
21 สิงหาคม 2552 11:57try 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
24 สิงหาคม 2552 2:01ผู้ดูแล
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.
26 สิงหาคม 2552 6:30
Thank you.. It workrd.. :)
8 สิงหาคม 2555 13:21
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