Attached and Detached

Question

Post reply

Attached and Detached

Chris Melton

SSC Veteran

Points: 217
More actions
October 8, 2004 at 10:03 am

#85621

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jmcgarvey Default port Points: 1489 More actions · Answer 1

The following script will tell you what is attached. From that you can figure out what is not.

use master

go

exec sp_MSforeachdb @command1 = 'use ?;select convert(varchar(30),name) as Name,filename from sysfiles'

Chris Melton SSC Veteran Points: 217 More actions · Answer 2

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.

hoo-t SSChampion Points: 12713 More actions · Answer 3

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.

DECLARE

@command varchar(500),

@path varchar(255)

Create Table #file_table

( #file_name varchar(100) null )

set @path = 'E:\SQL_Data\'

select @command = 'master..xp_cmdshell ' + '"' + 'dir ' + @path + '/b /A:-D' + '"'

insert #file_table

exec (@command)

-- Steve

barsuk SSChampion Points: 12280 More actions · Answer 4

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.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

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.

Yelena

Regards,Yelena Varsha

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

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).