Sql server Named Instance

Question

Sql server Named Instance

vs.satheesh

SSCrazy

Points: 2392
More actions
June 3, 2015 at 3:53 am

#318683

Hi
In my development box i have more then 10 named instance.But All the instance not properly maintained mdf and ldf file. In this folder some of the log file occupied more then 200 GB.But i don't know which named instance referring which instance. How to find instance name based on MDF and LDF file?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You'll need to query sys.master_files on each instance to get a list of the mdf and ldf files associated with each instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

In SQL Server Management Studio view "Registered Servers". Create a folder for your 10 instances and register each server instance. Once all 10 are added right-click on the folder and select "New Query". That will open up a new query window connected to all 10 instances. Then do a query against sys.master_files and you will get results from all 10 instances simultaneously along with an added column indicating the instance each row is related to.

SELECT DB_NAME(database_id) AS "DB_Name", name AS "Logical_File_Name", [physical_name] AS "Physical_File_Name"

FROM sys.master_files

ORDER BY "DB_Name", [file_id];

Joie Andrew
"Since 1982"

Lowell SSC Guru Points: 323518 More actions · Answer 3

Joie Andrew (6/3/2015)
In SQL Server Management Studio view "Registered Servers". Create a folder for your 10 instances and register each server instance. Once all 10 are added right-click on the folder and select "New Query". That will open up a new query window connected to all 10 instances. Then do a query against sys.master_files and you will get results from all 10 instances simultaneously along with an added column indicating the instance each row is related to.
SELECT DB_NAME(database_id) AS "DB_Name", name AS "Logical_File_Name", [physical_name] AS "Physical_File_Name"
FROM sys.master_files
ORDER BY "DB_Name", [file_id];

excellent advice.

this is exactly how I do it; i utilize both Registered Servers and a Central Management Server, and it makes it trivial to query tens or hundreds of servers with the same query, and get the results in a single grid.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!