I have a very large number of databases spanned across several large hard disks. I need to create a script that will tell me what is currently attached, and what is currently detached. This will aid in space management.
The following script will tell you what is attached. From that you can figure out what is not.
exec sp_MSforeachdb @command1 = 'use ?;select convert(varchar(30),name) as Name,filename from sysfiles'
I appreciate it, but I have about 200 servers with about 100 databases on each. I was actually looking for a script that would give me both attached and unattached.
If your file locations are consistent, here's a little script that will give you the physical file names on the server. From there, you can query sysfiles to learn which are attached and which are not.
Create Table #file_table
( #file_name varchar(100) null )
set @path = 'E:\SQL_Data\'
select @command = 'master..xp_cmdshell ' + '"' + 'dir ' + @path + '/b /A:-D' + '"'
How do you handle all servers?
The way we do (and we have about the same number of servers) is to use link servers management.
We designate one server for handling that. This server has a table(s) with a list of all our Servers.
While we don`t handling specific requirement you could loop thru all your servers and execute the following command:
SELECT DATABASEPROPERTY ('MetaData','IsDetached') storing the output in the temp table.
Then you run a job every hour, sending the output to DBAs.
If you have SQL Servers list in the database you may run a query that will get a servername from the database, append strings before and after and output in the results window a set of statements like this:
SELECT * FROM OPENROWSET('SQLOLEDB','Provider=SQLOLEDB;Server=MyServerName123;Trusted_Connection=yes','select @@servername, <put here everything you want to select according to postings above')
Then copy the output from the results window to the query window and run. It will dynamically connect to all servers without actually having linked servers set up and return results for them if your domain account have rights on all servers. if the connection fails for 1 server it will still get other servers.
One Orange Chip
The previous post can help you list the names of detached databases, but not the detached files that I think you're looking for.
It sounds like you have a large file system (SAN?) that can have database files for any of 200 servers. You need a list of all the MDF, NDF, and LDF files on there whether attached or not. Hope you didn't use any non-standard extensions! Make a table of all the files on the system:
CREATE TABLE #SQLFILES (Filename varchar(1023) null)
INSERT INTO #SQLFILES EXEC xp_cmdshell('dir f:\ /s /b')
DELETE #SQLFILES WHERE RIGHT(Filename,3) NOT IN ('MDF', 'NDF', 'LDF') OR Filename IS NULL
Repeat the INSERT - EXEC for each drive letter used by SQL Server. I hope you were consistent across servers, if not use "SELECT DISTINCT LEFT(filename,1) FROM master..sysaltfiles" to list drive letters and use them to generate the INSERT - EXECs dynamically.
Then join this table to sysaltfiles to find the files that are currently attached.
SELECT Filename, CASE WHEN b.Filename IS NULL THEN 'Detached' ELSE 'Attached' END AS [Status]
FROM #SQLFILES a
LEFT JOIN master..sysaltfiles b ON a.Filename = b.Filename
Put this in an sproc on each server and call them from your admin system to build the complete list. You probably want to elaborate on this a little, maybe add @@SERVERNAME to the query and/or replace the drive letters in the filenames with the UNC shares the drives are mapped to.
I haven't graduated to a SAN with mountpoints, I don't know how that would affect this process. I'm also assuming that the file shares used by each server are unique to that server, and that the SQL Server file shares are not also used extensively for other purposes (i.e. the "DIR /s" should not return an excessive number of rows).
Viewing 7 posts - 1 through 6 (of 6 total)