Unused/detached files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Unused/detached files

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-01-31 : 18:19:36

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 19:37:01

look in the operating system for those file extensions and compare to the results from this query

SELECT DB_NAME(dbid) AS databasename, fileid, name, filename
FROM
master.dbo.sysaltfiles

The server won't likely have information on detached files.

Any mdf or ldf file you could also try to cut from the directory and the cut would fail because it was "in use" by the db it was attached to.

Poor planning on your part does not constitute an emergency on my part.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-02-01 : 10:14:07

Thanks for reply.
So there is no script or other way to find all detached files?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 17:27:55

No via SQL server. You have to check the file system.

Poor planning on your part does not constitute an emergency on my part.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-01 : 17:42:13

Here's a script I wrote a while ago that might work for you:

declare @sql varchar(8000)
select @sql=''
create table #dbfiles(db varchar(50) not null, name varchar(50) not null, filename varchar(128) not null)
create table #files(filename varchar(128) null)
exec sp_msforeachdb 'insert into #dbfiles(db, name, filename) select ''?'', name, filename from ?..sysfiles'
select @sql=@sql+'exec master..xp_cmdshell ''for %a in (' + a.path + '*.*) do @echo %a'';' 
from (select distinct reverse(substring(reverse(filename), charindex('\',reverse(filename)), 8000)) as path from #dbfiles) a
insert into #files exec(@sql)
select f.* from #files f left join #dbfiles d on f.filename=d.filename where d.filename is null
drop table #files
drop table #dbfiles

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources