@@SERVICENAME and the SQL Server Service Name

@@SERVICENAME and the SQL Server Service Name: the T-SQL from this post in SQL Server Management Studio with its results

Applies to SQL Server 2019, 2022 and 2025.

Every instance has a SQL Server service name, and @@SERVICENAME is the quick way to ask an instance what its name is from T-SQL. It’s a small function, but I use it in scripts that need to work the same way on default and named instances, and it has one trap that catches people out.

What @@SERVICENAME returns for the SQL Server service name

@@SERVICENAME takes no arguments and returns an nvarchar value. On a default instance it returns MSSQLSERVER. On a named instance it returns the instance name only: connect to SQLPROD02\FINANCE and you get FINANCE.

That’s the part people misremember. The Windows service for that named instance is called MSSQL$FINANCE, and its Agent is SQLAgent$FINANCE, but @@SERVICENAME doesn’t give you the prefix. You have to add it yourself.

A worked example

Here I’ve put three connection names in a temp table and worked out, for each one, what @@SERVICENAME would return on that instance and the names you’d build from it: the engine service, the Agent service and the performance counter prefix.

CREATE TABLE #Instances (ConnectName nvarchar(128) NOT NULL);

INSERT INTO #Instances (ConnectName)
VALUES (N'SQLPROD01'), (N'SQLPROD02\FINANCE'), (N'SQLDEV01\SQL2025');

SELECT i.ConnectName,
       v.ServiceValue AS ServiceNameValue,
       CASE WHEN v.ServiceValue = N'MSSQLSERVER' THEN N'MSSQLSERVER'
            ELSE N'MSSQL$' + v.ServiceValue END AS EngineService,
       CASE WHEN v.ServiceValue = N'MSSQLSERVER' THEN N'SQLSERVERAGENT'
            ELSE N'SQLAgent$' + v.ServiceValue END AS AgentService,
       CASE WHEN v.ServiceValue = N'MSSQLSERVER' THEN N'SQLServer:'
            ELSE N'MSSQL$' + v.ServiceValue + N':' END AS CounterPrefix
FROM #Instances AS i
CROSS APPLY (SELECT CASE WHEN CHARINDEX(N'\', i.ConnectName) = 0 THEN N'MSSQLSERVER'
                         ELSE SUBSTRING(i.ConnectName, CHARINDEX(N'\', i.ConnectName) + 1, 128)
                    END) AS v (ServiceValue);

The default instance SQLPROD01 comes back as MSSQLSERVER, SQLSERVERAGENT and SQLServer:. The two named instances come back as MSSQL$FINANCE, SQLAgent$FINANCE and MSSQL$FINANCE:, and the same pattern for SQL2025.

On a live instance you don’t need the table. This compares @@SERVICENAME with the other name functions, then uses it to pick the right counter prefix:

SELECT @@SERVICENAME                  AS ServiceNameValue,
       @@SERVERNAME                   AS ServerNameValue,
       SERVERPROPERTY('InstanceName') AS InstanceNameProperty,
       SERVERPROPERTY('MachineName')  AS MachineNameProperty;

DECLARE @Prefix nvarchar(140) =
    CASE WHEN @@SERVICENAME = N'MSSQLSERVER' THEN N'SQLServer:'
         ELSE N'MSSQL$' + @@SERVICENAME + N':' END;

SELECT TOP (5) RTRIM(object_name) AS ObjectName, RTRIM(counter_name) AS CounterName, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE @Prefix + N'%';

I mostly use the counter prefix trick: sys.dm_os_performance_counters names its objects SQLServer:Buffer Manager on a default instance but MSSQL$FINANCE:Buffer Manager on a named one, so a hard-coded filter returns nothing on half your estate. My performance counter script does exactly this.

The common mistake

The mistake is treating @@SERVICENAME as the Windows service name. On a default instance the two happen to match, so a script written and tested there looks fine. Run it on a named instance and net stop FINANCE or a lookup for a service called FINANCE fails, because the real service is MSSQL$FINANCE.

The second slip is mixing it up with SERVERPROPERTY('InstanceName'). They agree on a named instance, but on a default instance InstanceName returns NULL while @@SERVICENAME returns MSSQLSERVER. If you concatenate the property into a string without handling NULL, the whole string becomes NULL.

When you need the exact Windows service names, and their accounts and status, ask sys.dm_server_services instead of building them. The names above are the standard pattern, which is fine for counters and scripts, but the DMV tells you what’s really installed.

Watch out for

  • SQL Server on Linux and containers. There’s only the default instance, so @@SERVICENAME returns MSSQLSERVER.
  • @@SERVERNAME is not the same thing. It returns SERVER\INSTANCE and can be out of date after a Windows rename until you fix it with sp_dropserver and sp_addserver. @@SERVICENAME doesn’t depend on that.
  • Padded counter names. object_name and counter_name in the counters DMV are fixed-width nchar, so trim them before comparing with =.
  • Service names on the command line. When you stop or start a named instance from the command line, use MSSQL$NAME, as in my post on starting SQL Server with minimal configuration.

Microsoft’s reference page is here: @@SERVICENAME (Transact-SQL). For more day-to-day DBA scripts, see my SQL Server DBA Handbook.

This topic came up in the SQLServerCentral Question of the Day: The Service Name.

Clive Roberts DBA