Data base Alert - instance sample_server, long queries is not available for version - sqlserver probe
search cancel

Data base Alert - instance sample_server, long queries is not available for version - sqlserver probe

book

Article ID: 199363

calendar_today

Updated On: 12-29-2023

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

xxx, instance sample_server, long queries is not available for version
or
Profile <profile_name>, instance localhost, long queries is not available for version n/a

Environment

  • Release: UIM 20x or later
  • Component: UIM - SQLSERVER

Resolution

1. Increase the profile timeout

2. The user specified in the database connection was incorrectly specified.
     It was  DOMAIN.xx.yy\username when it should have been DOMAIN\username. The user was verified using the xp_logininfo sql command

    This is the query used for long_queries: (what are the results when run manually?)

"SELECT r.session_id spid, s.host_process_id hostid, s.host_name, s.login_name loginname, s.program_name, (convert(dec(15,3),r.cpu_time))/1000 cpu_time, (convert(dec(15,3),r.total_elapsed_time))/1000 elapsed_time,   convert(varchar(3000),isNull(SUBSTRING (st.text,1,3000),'n/a')) as sql_text FROM sys.dm_exec_sessions s, sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st where  s.session_id = r.session_id and r.status = 'running' and s.session_id <> @@SPID"

3. Please check if the user has permission to run this query.
    In older versions, the probe ignored the configured user and used the administrator user by default to establish the connection with the database.
    Hence you did not see this problem. 

sqlserver probe - Access Prerequisites

    Double-check the following permissions with the db admin to be sure that they are in place.

  • Right-click on the table and go to Properties -> Permissions Tab.
  • Select Add and Browse for the user to grant permission.
  • Click OK.
  • Select the permission type under the Grant column. User is mapped for the required permission.

    User mapping is required for the following tables:

    master.sys.databases
    master.dbo.sysperfinfo
    msdb.dbo.sysjobsteps
    msdb.dbo.sysjobs
    msdb.dbo.syscategories
    msdb.dbo.log_shipping_monitor_secondary
    msdb.dbo.log_shipping_monitor_primary
    msdb.dbo.sysjobhistory
    .sys.database_files
    .sys.partitions
    .sys.allocation_units
    .sys.internal_tables
    .sys.filegroups