June 11, 2009 at 4:06 pm
I have already developed a solution to this but I would like to determine if anyone out there has a more elegant solution than mine. I will share mine later and compare notes. Read on below:
Your manager comes to you late on a Friday afternoon and gives you the following information:
"On DBServer1 there are currently over 200 active dbs on that server. Six months ago the prior DBA (who is no longer there) detached over 100 other dbs on that server for our downsizing effort. But I had told him explicitly not to physically delete the files. Just leave them in place for now just in case an app breaks down the line and we need to quickly bring any of them back into service. Well, after six months I am now confidant that those detached db files can now be safely removed, thus giving us back needed disk space on that box. However, before they are removed this is what I want from you:"
"All data(.mdf), log(.ldf), and secondary files(.ndf) currently exist in the D:\MSSQL\DATA directory. No where else. There are currently hundreds of files in that directory. Therefore, I need you to provide me a list in 15 minutes prior to a meeting that will identify all database files that currently exist in that directory that are NOT currently connected to Active databases. In otherwords, I want a list of just detached files only. I do not need to see attached files..." Time is of the essence..:w00t:
How would you go about collecting this information in a timely matter? Include code for your solution..I want specifics not generalities. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 11, 2009 at 4:53 pm
If I was in hurry I would go with something like....
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
create table #filesInUse
(
filenamesysnamenot null
)
create table #filesInFolder
(
filenamesysname null
)
DECLARE cur CURSOR
READ_ONLY
FOR select name from master.dbo.sysdatabases
DECLARE @name varchar(40)
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
exec('insert into #filesInUse select filename from ' + @name + '.dbo.sysfiles')
END
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
GO
declare @BaseFolder sysname
set @BaseFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'
declare @cmd varchar(1000)
set @cmd = 'dir "' + @BaseFolder + '" /B';
insert into #filesInFolder
EXEC xp_cmdshell @cmd
select * from #filesInFolder f
where not exists ( select null from #filesInUse u where @BaseFolder + '\' + f.filename = u.filename)
and RIGHT(f.filename,4) in ('.mdf','.ldf')
drop table #filesInUse
drop table #filesInFolder
June 11, 2009 at 6:05 pm
Good job! You are very close to what I have but mine is a little different. You used a CURSOR walking all sysdatabases and selecting the singleton table sysfiles to get FilesInUse and then you load that set into a temp table. For that first set I got the same thing set without using a cursor and a temp table by simply doing this:
SELECT
af.[filename] as [Files In Use]
FROM master.dbo.sysaltfiles af
INNER JOIN master.dbo.sysdatabases db ON af.dbid = db.dbid
ORDER BY af.[filename]
go
Much simpler and much less overhead, Now onto the second step, FilesInFolder. I am a little closer to you on this one but still a little different:
declare @path varchar(200)
set @path = 'D:\MSSQL\DATA\'
declare @myquery varchar(1000)
declare @query varchar(1000)
declare @name varchar(1000)
create table #FilesInFolder (id int identity(1,1) ,name varchar(1000))
--Get ALL db files in Folder. Note: You are not accounting for .ndf files I am..
set @myquery = 'dir ' + @path + '*.?df /a'
--Load up the Temp table
insert #FilesInFolder(name)
exec master..xp_cmdshell @myquery
-- Strip out all directory garbage
delete from #FilesInFolder where substring(name,3,1) '/' or name is null or
substring(name,25,1) ='<'
DELETE #FilesInFolder WHERE
SUBSTRING(Name,1,2) '99' OR
Name IS NULL
-- Now look at second result set
select @path + substring(ltrim(rtrim(name)),40,30) from #FilesInFolder
Now it is just a matter of putting together the 2 records sets and determining what files in #FilesInFolder temp table are NOT IN the FilesInUse record set and you got your orphaned files. I accomplished this with one table and one temp table using an EXCEPT where you used a NOT EXISTS:
select @path + substring(ltrim(rtrim(name)),40,30) AS [Detached Files] from #FilesInFolder
EXCEPT
select
af.[filename] AS [FilesInUse]
FROM master.dbo.sysaltfiles af
INNER JOIN master.dbo.sysdatabases db ON af.dbid = db.dbid
drop table #FilesInFolder
go
Good job!!! 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 12, 2009 at 8:42 am
Although this is posted in SQL Server 7/2000 forum, I don't have a SQL Server 2000 system available, so the following solution is for SQL Server 2005. With a few mods and access to a SQL Server 2000 system, I could probably get it working quickly there as well.
create table #Files (
DBFiles varchar(256)
);
insert into #Files
exec xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\*.?df" /b'
delete from #Files
where
DBFiles like 'mast%' or
DBFiles like 'mssqlsys%' or
DBFiles like 'model%' or
DBFiles like 'msdb%' or
DBFiles like 'dist%' or
DBFiles is null;
with DatabaseFiles as (
select
reverse(left(reverse(physical_name), charindex('\',reverse(physical_name)) -1)) DBFiles
from
sys.master_files
where
physical_name like 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data%'
)
select
os.DBFiles
from
#Files os
left outer join DatabaseFiles db
on (os.DBFiles = db.DBFiles)
where
db.DBFiles is null;
drop table #Files;
June 12, 2009 at 9:09 am
Similarly, I'd use xp_cmdshell to get the dir, with a /b tag to eliminate the overhead on that, then I'd query the files in use.
In SQL 2005, I'd use Except to eliminate the attached files. In 2000, I'd use a left join or "where not in".
You can also get a list of what files are in use by SQL Server using MS Process Explorer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 9:18 am
Gus,
You are correct, the EXCEPT would have been easier, but I was trying to keep it close to SQL Server 2000. Only changes needed would be to the appropriate system table, change the CTE to a derived table, and perhaps a column name change may be needed as well.
June 12, 2009 at 12:32 pm
Lynn,
Unfortunately after testing, your solution does not find DETACHED files in a directory I supplied. Old Hand's and mine does. Remember guys, we don't care about listing all db files already in use. Only detached files. You correct though, LEFT JOIN on NOT IN, or NOT EXIST will also work in place of EXCEPT in other versions. However, the correct timely solution to the problem is the emphasis here. Not so much what version you are dealing with. I realize this is a SQL 2000 forum though...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 12, 2009 at 12:39 pm
GSquared,
The problem is not to identify Files in use,it is to provide a QUICK list of ALL detached database files ONLY in a directory. MS Process Explorer does not give you this, it gives you files in use. Anyway it is not practical to look manually through a directory of hundreds of database files with an application like Process Explorer in order to determine what files are not in use when your manager needs the hard copy list in 15 minutes...Remember guys, there is a time constraint on this problem.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 12, 2009 at 1:01 pm
talltop (6/12/2009)
GSquared,The problem is not to identify Files in use,it is to provide a QUICK list of ALL detached database files ONLY in a directory. MS Process Explorer does not give you this, it gives you files in use. Anyeay it is not practical to look manually through a directory of hundreds of database files with an application like Process Explorer in order to determine what files are not in use when your manager needs the list in 15 minutes...Remember there is a time constraint on this problem.
Yes, I was merely including that as an aside.
Here's how I'd do it on my desktop (SQL 2005 Dev Edition):
if object_id(N'tempdb..#T') is not null
drop table #T;
declare @Cmd varchar(1000);
select @Cmd = 'dir "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" /b'
create table #T (
ID int identity primary key,
FName varchar(1000));
insert into #T (FName)
exec xp_cmdshell @cmd;
select 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\' + FName
from #T
where FName like '%df'
and FName not like 'distmdl._df'
and FName not like 'mssqlsystemresource._df'
except
select physical_name
from sys.master_files;
For SQL 2000, I'd have to change the final query to a left outer join, and to the file tables from 2000, but I don't have a copy of 2000 available to do that with, so I can't test it. Would be simple enough to accomplish.
Altogether, that query took me 2 minutes to write and less than a second to run. Would take a little bit longer with a few hundred files, but not much.
Personally, if I disconnect a database, I'd move the files into a separate directory right then and there. Easy enough to revert, and saves effort later. I'd also avoid having MDF and LDF files on the same drive, much less the same directory, on a production server, in almost all cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 1:12 pm
The problem states that the manager did not want the files moved to another directory and the prior DBA honored that. However, I would have done the same as you would of, moved the files. However, that was not the problem at hand. Also, I tested your 2005 version on my directory before I detached some files and then after as well. The list I get back is identical in both cases listing all db files in the directory detached or not, which does not solve the problem. If there are not detached files in the directory, then no rows should be returned. If there are detached files, then only detached files should be returned in the list. Hold on strike that, I got it working now...There are 2 things I like about your solution though. No Cursors and it is short!!!! Good job!!!!! You also took advantage of the EXCEPT like I did. Better performnce if you are dealing with a lot of files...check out the xplans on the EXCEPT..Also I forgot to mention, this was a TESTBED server for ALL of development and they were pretty much out of space, not a production server which explains the drives and directories...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 12, 2009 at 1:13 pm
Curious. The code Iprovided found all the mdf/ldf files in my data directory that had been detached from SQL Server on my desktop system with no problems. Makes me wonder how you ran your tests.
June 12, 2009 at 1:26 pm
I ended up with a mixed solution. I find it simple compared with those you proposed:
Declare @Path varchar(256)
declare @myquery varchar(1000)
Set @Path = 'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
set @myquery = 'dir "' + @path + '*.?df" /b'
create table #Files (DBFiles varchar(256))
insert into #Files
exec xp_cmdshell @myquery
select @path + DBFiles AS [Detached Files] from #Files where DBFiles IS NOT NULL
EXCEPT
select
af.[filename] AS [FilesInUse]
FROM master.dbo.sysaltfiles af
INNER JOIN master.dbo.sysdatabases db ON af.dbid = db.dbid
Tha cleaning code its kind of tricky, with the /b option you get only what you want. After that a simple query and thats it.
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 12, 2009 at 2:05 pm
My first idea on reading the OP was a simple directory listing of the DATA directory - sort by date. I would probably find a bunch of files with last modified dates about 6 months ago. Those are the files to list out. Thats the simple 30-second solution.
Then I read the rest of the thread - And I'll stick with my original idea. 😉
June 12, 2009 at 9:22 pm
I believe I'd tell the manager off. It's not his neck if data is inappropriately deleted... it's mine.
I would insist on moving the data to a subdirectory off the main directory. That takes no time at all because DOS is smart enough to realize it's on the same disk and just change the handle to point to the new directory. Of course, active DB's won't move because they're being used. Then I could do three things very easily and safely... 1. Provide the list of file names that were going to be deleted. 2. Do a final backup of all those items that were going to be deleted. 3. Safely delete all those items without fear of selecting the wrong item if someone where foolish enough to put something besides and MDF/LDF/NDF file in the main directory.
DBA has one job and only one job. Protect all data... even the stuff you're deleting especially if you're following ISO or SOX rules.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2009 at 12:58 pm
Jeff,
Although I totally agree with your premise on the way to properly do things, that was not the current situation being faced. This was the situation at hand and a quick solution had to be found. As you traverse your DBA career, you will run into situations that are not always by the book. That's real world. You then will have to adapt and deal with the issue at hand and there have been some eloquent ways presented here by some on how to find and list detached files. Good job guys! That is all part of being a seasoned DBA and this is ALWAYS a good script to have handy in your toolbox. As to telling off your manager, that is never a good idea. Particularly in these days and times when lays offs are rampant. Again, if you read the original post this was a situation that I walked into. I did not create this situation. If I was the DBA at that time I would have also firmly urged moving the files as I said before, but would I have told off my manager over detached files? I don't think so...You have to carefully pick your battles.....:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply