January 9, 2009 at 11:02 am
I trying to gain more space on my disks on different servers. I observed that there are many database files which are not used anymore on different drives, those files which were detacched from DB but never attached later as we are not using them anymore. Now i want get rid of those files.
Is there an easy where where i can find such files and delete them instead of gettign into each drive and checking each file.
January 9, 2009 at 11:57 am
I don't think there's an easy way.
My only suggestion is to use Windows search for .mdf and .ldf and cross reference it with your output from select physical_name from sys.master_files.
If you're a clever scripter you could maybe use xp_cmdshell and the dir command to find them all and pump them into a table to do the compare for you, then output the delete syntax.
Good luck,
~BOT
Craig Outcalt
January 9, 2009 at 12:01 pm
Run this to find all the Data & Log files currently in use:
exec sp_MSforeachdb 'Select ''?'' as DB_Name, * from ?.dbo.Sysfiles'
Then scan your drives for .MDF, .NDF and .LDF files and if they're not in the above list then maybe they can be deleted.
January 9, 2009 at 9:54 pm
Mike Levan (1/9/2009)
I trying to gain more space on my disks on different servers. I observed that there are many database files which are not used anymore on different drives, those files which were detacched from DB but never attached later as we are not using them anymore. Now i want get rid of those files.Is there an easy where where i can find such files and delete them instead of gettign into each drive and checking each file.
-- To find Database space
sp_msforeachdb 'dbcc showfilestats'
GO
sp_msforeachdb 'sp_spaceused'
GO
sp_databases
GO
-- to find disk drive space
xp_fixeddrives
January 14, 2009 at 7:37 am
I ran this query
exec sp_MSforeachdb 'Select ''?'' as DB_Name, * from ?.dbo.Sysfiles'
and got all the database files listed , that looks good but
the result was in seperate grid for each db, how can i get in single result set or in a table all together.
January 14, 2009 at 7:48 am
You could create a global temp table (##) that had the same format as the output of a single query. Within your sp_msforeachdb you would do an insert into that table, and then after the execution of that you could select * from that table. That should get it for you.
January 15, 2009 at 11:14 pm
Mike Levan (1/14/2009)
I ran this queryexec sp_MSforeachdb 'Select ''?'' as DB_Name, * from ?.dbo.Sysfiles'
and got all the database files listed , that looks good but
the result was in seperate grid for each db, how can i get in single result set or in a table all together.
U can insert in one table , then u can find total results from table..
January 16, 2009 at 7:24 pm
how can i do that into a temp table.
January 18, 2009 at 3:37 pm
SQLBOT (1/9/2009)
I don't think there's an easy way.My only suggestion is to use Windows search for .mdf and .ldf and cross reference it with your output from select physical_name from sys.master_files.
If you're a clever scripter you could maybe use xp_cmdshell and the dir command to find them all and pump them into a table to do the compare for you, then output the delete syntax.
Good luck,
~BOT
Sorry for asking this but could you please let me know how to script out this as you said.
thanks for the help.
January 18, 2009 at 6:04 pm
You can use the following to get the data from sysfiles in each db into a temp table.
If the databases are attached their files will be locked (mdf, ndf, ldf) so you won't be able to move or delete the files. That's a good thing. Take the results from the query below, open a file manager, create a folder at the root of each volume (call it Junk or something) then start moving files that aren't in the list into the folder. If you accidentally grab one that is attached the OS will let you know and you won't be able to move it.
-- drop temp table if it exists from previous run
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t
(
DB_Name sysname
,fileid smallint
,groupid smallint
,size int
,maxsize int
,growth int
,status int
,perf int
,name sysname
,filename nvarchar(260)
)
--now load the temp table
INSERT INTO #t
exec sp_MSforeachdb 'Select ''?'' as DB_Name, * from ?.dbo.sysfiles'
-- and look at the data
SELECT * FROM #t
--clean up
DROP TABLE #t
January 18, 2009 at 6:30 pm
I would be hesitant to try the following since there is the potential to do quite a bit of damage but I thought I might mention you could use the dos del command to delete files like *.?df. There is an /S switch to delete from subdirs. Open a cmd window and type del /? to see the help info.
Again this will rely on attached data files being locked.
I would personally go through the directories by hand but you might be braver than me. 😀
January 19, 2009 at 9:42 am
Eric Klovning (1/18/2009)
I would be hesitant to try the following since there is the potential to do quite a bit of damage but I thought I might mention you could use the dos del command to delete files like *.?df. There is an /S switch to delete from subdirs. Open a cmd window and type del /? to see the help info.Again this will rely on attached data files being locked.
I would personally go through the directories by hand but you might be more reckless than me. 😀
Fixed 😛
January 19, 2009 at 7:33 pm
Thats a gud idea buti wud like to know something if tht is possible?
Can select files something like
" select files from the drive where not in temp table #t " if this is possible i can just get rid of those files from the result set.
February 13, 2009 at 7:12 am
Yes I would try to create a junk folder in each volume and just select all the files in the folder and try to move them to junk folder, if the files are attached to some db then they will not move others will move, i guess?
hows that? do you guys predict any danger in doing this 😎
February 13, 2009 at 7:24 am
Just my 2 cents, use the windows search (can scan network drives and multiple drives without making a complicated loop).
Then make sure those files are not needed
Get authorisation to delete the file AFTER BACKING UP TO DVD OR DRIVE.
Then delete and document.
You never know when someone will come back too ask for that data, assume it'll happen because it very well could.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply