How to check which storage belongs to which instance?

Question

Post reply

How to check which storage belongs to which instance?

san43

Hall of Fame

Points: 3196
More actions
March 23, 2006 at 3:44 am

#365581

How to check which storage belongs to which instance and is active using a query?

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

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

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

select * from sysfiles

will give you the path to the physical file for a database.

What do you mean "is active"? Are you talking about the cluster?

Regards,Yelena Varsha

san43 Hall of Fame Points: 3196 More actions · Answer 2

I mean if a sever has two or three SQL Server Instances installed ,they will installed in seperate drives or same drive? Also, .mdf and .ldf files of master database are stored in seperate storage for different instances means seperate drives.

Hope I was expressing in correct way.

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

The default location for database files will be different (under the install folders, under C:\Program Files), but hopefully someone has had the sense to move them somewhere else. Whether separate folders were created and used for each instance depends on the DBA.

The query "select filename from sysfiles" will tell you where the files for a database are located. The query "select filename from master.dbo.sysaltfiles" will tell you where all mdf, ldf, and ndf files for all databases in that instance.

If the default instance has linked server definitions for the other instances, you could find all the files with the query:

select 'default' as instance, filename from master.dbo.sysaltfiles

union all select '<inst1>', filename from <inst1>.master.dbo.sysaltfiles

union all select '<inst2>', filename from <inst2>.master.dbo.sysaltfiles

...

san43 Hall of Fame Points: 3196 More actions · Answer 4

I got this, but how do I know that storage is active?

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

I'm still not sure what you're looking for, but if you're talking about actual read/write activity during a query you could try the Windows performance monitor and trace reads/sec, writes/sec, and disk queue length, but you can only drill down to the drive level. You could use SQL Profiler to get reads, writes, and cpu load for each query. These are programs you have already, and they can tell you which disks are working hardest and which queries are responsible.

If you want more detailed info there are monitoring tools like Quest's Spotlight on SQL Server. (There are other tools available but this is the one I am familiar with.) It will let you drill down an see I/O activity levels for each database file in real time. You can download a time-limited free trial (fully-functional) from their website.