Uploaded image for project: 'Jira Service Management Data Center'
  1. Jira Service Management Data Center
  2. JSDSERVER-11955

Insight MSSQL Database Pattern to scan multiple Database Instances

XMLWordPrintable

    • 1
    • 9
    • We collect Jira Service Desk feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Currently, the sqlcmd connects only to the default DB instance on the server. If there are other instances on the server, they will not be scanned. The solution for Windows Servers with SQL Server is as follows:

      • Integrate into the C# Code an additional loop using the following command:
        --> reg.exe query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL"

      This will render the list of instances on the server as follows:

      HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
      INSTANCE1 REG_SZ MSSQL15.INSTANCE1
      INSTANCE2 REG_SZ MSSQL15.INSTANCE2

      Using the string Instance Names "INSTANCE1" and "INSTANCE2" then for each the following command can be executed (note that we prepended the connection to the localhost IP and InstanceName - this is where the Instance Names from the first command need to be injected):

       

      sqlcmd -S 127.0.0.1\INSTANCE1 -E -Q "SELECT DB.name, SUM(CASE WHEN type = 0 THEN cast(MF.size as bigint) * 8 / 1024 ELSE 0 END) AS DataFileSizeMB, SUM(CASE WHEN type = 1 THEN cast(MF.size as bigint) * 8 / 1024 ELSE 0 END) AS LogFileSizeMB, @@servicename AS InstanceName, (SELECT TOP 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL ORDER BY last_read DESC) AS Port FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id GROUP BY DB.name"

sqlcmd -S 127.0.0.1\INSTANCE2 -E -Q "SELECT DB.name, SUM(CASE WHEN type = 0 THEN cast(MF.size as bigint) * 8 / 1024 ELSE 0 END) AS DataFileSizeMB, SUM(CASE WHEN type = 1 THEN cast(MF.size as bigint) * 8 / 1024 ELSE 0 END) AS LogFileSizeMB, @@servicename AS InstanceName, (SELECT TOP 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL ORDER BY last_read DESC) AS Port FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id GROUP BY DB.name"
 

      Adapting the pattern file and C# code will help us to scan all the DB instances.

            [JSDSERVER-11955] Insight MSSQL Database Pattern to scan multiple Database Instances

            There are no comments yet on this issue.

              11507b174037 Zakhar Listiev
              f75285566e0c Prabhu Subramaniyan
              Votes:
              18 Vote for this issue
              Watchers:
              8 Start watching this issue

                Created:
                Updated: