Deleting unused mdf and ldf files

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

General SQL Server Forums

New to SQL Server Programming

Deleting unused mdf and ldf files

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-31 : 09:55:15

Hi,

I have a server that is getting low on space. Its a test server so there is a lot of junk and remnants spread out. I am looking for ideas on cleaning duplicate mdf and ldf files. For example MDF/LDF files that are not linked to active databases. I'd love to delete those. Any links or thoughts would be magnificent!

Craig Greenwood

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-31 : 11:55:58

For the record I found this query useful. You have to play with the where statement in the second query to dictate the drives you want to look at. Highly recommended:

[code]

use master
create table #temp
(
dbname varchar (200) NULL,
name varchar (200),
fileid int,
location varchar (250),
filegroup varchar (15),
size varchar (20),
maxsize varchar (20),
growth varchar (20),
usage varchar (20))

declare @dbname sysname
declare @cmd nvarchar(300)

declare c cursor for

select name from master.dbo.sysdatabases where status < 499 or status > 599

open c
fetch next from c into @dbname
while @@fetch_status = 0
begin

select @cmd = N'use ' + quotename(@dbname) + N' INSERT INTO #TEMP (name, fileid, location, filegroup, size , maxsize , growth, usage) exec sp_helpfile '
exec (@cmd)
UPDATE #TEMP SET DBNAME = @DBNAME WHERE DBNAME IS NULL
fetch next from c into @dbname
end

close c
deallocate c

SELECT dbname, name, filegroup, location
FROM #TEMP
WHERE usage like '%data%' and location not like 'G:%'
or usage like '%log%' and location not like 'I:%'
order by dbname, name

DROP TABLE #TEMP

[\code]

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources