I've found one of my SQL servers has all the usual Sharepoint Search Server application databases but I can't figure out why they are on this particular SQL server.

Is there a SQL query that I can use to find out the name of the Sharepoint server? Or perhaps a Powershell command I can run?

  • How many servers in your farm ? should you elaborate more what do you need specifically for I can't figure out why they are on this particular SQL server. ? – Mohamed El-Qassas MVP Nov 28 '16 at 13:28
  • 1. It is a self contained server. I think I may have created a test SharePoint instance but I can't remember where. I've just come across the databases though so want to trace my instance. – Syntax Error Nov 28 '16 at 13:31
  • you can confirm which instance of SQL your SharePoint been connected from Central Administration , if you have the access to SharePoint. – Esaki Nov 28 '16 at 13:37
  • What if I can't locate the SharePoint server? – Syntax Error Nov 28 '16 at 13:38
  • what you mean cant locate the SharePoint server? You dont know about SharePoint server, how you are accessing the SharePoint? – Waqas Sarwar MVP Nov 28 '16 at 13:48

from any SharePoint server run the below command, it will tell you on about every database is being used in the farm.

Get-SPDatabase | Select displayname, NormalizedDataSource, Type

Or go Central admin > Upgrade and migration > Review Database Status on this page, you will get list of database being used in SharePoint along with Sql Server information.

Couple of things to try to nail it down.

Run the below script, which will tell you any active connection with login name.

 SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Now if you run the below built in store proc then you will see from which server it is being connected.

exec sp_who
exec sp_who2
  • This only tells me about the server I'm connected to. What I'm trying to find is the mystery Sharepoint instance that has created databases on my server. Please re-read the question and comments – Syntax Error Nov 28 '16 at 14:50
  • are you seeing any active connection to that Search Database in SQL server? Do you have search service application in your farm? – Waqas Sarwar MVP Nov 28 '16 at 14:52
  • check th eupdated answer – Waqas Sarwar MVP Nov 28 '16 at 14:59
  • Got it! Running sp_who gave me various connections but more importantly a hostname. I've now tracked down the rogue Sharepoint server. Thanks :) – Syntax Error Nov 28 '16 at 15:04

From a SharePoint Server you can simply run

$db = (Get-SPDatabase)[0]

enter image description here

From SQL Server it's a bit tricker, but you can get the applications using this SQL Server by running the following TSQL command

SELECT program_name, count(*)
WHERE ecid=0
GROUP BY program_name
ORDER BY count(*) desc

Now the output doesn't give you the farm, but it gives you the applications. In my case

.Net SqlClient Data Provider                                                                                                    

I can see the Service Application name in the farm, in my case Cloud_Search_Service_Application, which makes it easier to identify which SharePoint farm is using this SQL Server. I have Hybrid Search switched on, which has a Clous Search Service Application named the same.

  • 1
    Hi Benny, this is helpful but just tells me I have a Sharepoint Foundation instance/farm. I need to know the server name but it seems that it might be a more difficult task than I thought! – Syntax Error Nov 28 '16 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.