sqlserver probe v5.x issues not available for version n/a

[Daniel Blanco]

Anyone else try the latest sqlserver version 5.x? We had 4.9x running with no issues but as soon as we tired 5.x we were flooded with these types of alerts:

Profile XXXX, instance SQL02, average fragmentation is not available for version n/a

Profile XXXX, instance SQL02, long_queires is not available for version n/a

Profile XXXX, instance SQL02, mirror instances is not available for version n/a

 

I have a case open but wondering if anyone knows what is causing this off hand...

[leima08]

Hi this also shows up for me in my tests. The workaround for now is to downgrade to 4.95. I would suggest you open a support case to track the issue and find a permanent solution, and not just a workaround. 

 

Thanks

[Daniel Blanco]

Hi, that's exactly what I did. I found that the credentials to all my SQL DB connections were also corrupted after the upgrade. I down graded back to 4.95 and all the existing db profiles then worked just fine. I have a case open already on this when I saw the n/a message flood. 

Really bad QA testing here..

[Stephen Danseglio]

Hi Dan,

 

Have you tried this?

 

- Deactivate the sqlserver probe
- Backup the sqlserver_monitor.cfg file and save it to a safe location outside the directory.
- Rt-click and delete the sqlserver probe
- Check to make sure the dir/files have been deleted on the robot file system
- Deploy sqlserver version 5.10 from scratch
- Restore the sqlserver_monitor.cfg file
- Activate the sqlserver probe and see if the problem persists.

 

Steve

[Daniel Blanco]

YES.

Case# 00583200

The sqlserver logs and the cfg were uploaded.

[Stephen Danseglio]

What type of corruption did you find, missing sections, corrupt characters, truncation, etc.? I have not seen this happen in this version before.

[Daniel Blanco]

Just checked the cfg from both version 5.x and 4.x and both have the same connections section content. 

In 4.95 when testing the connections they worked. In 5.10 test connections Failed. Reverted back and they worked. 

[Ashish Aggarwal]

Thanks for the feedback. CA Engineering is working on analyzing the issue and shall update on the solution soon.garav01

[anmol kumar]

Hi,

getting the sam issue for long queries and fragmentation instance. I am already using 5.10 version.

Please suggest what needs to be done.

Thanx

[Ashish Aggarwal]

Few checkpoints works only for Sql Server 2005 or higher version, Here are the checkpoints name…

1. AV_FRAGMENTATION
2. LOGIC_FRAGMENT
3. SCAN_DENSITY
4. LONG_QUERIES
5. LONG_JOBS
6. MIRROR_SQLINSTANCE
7. MIRROR_STATE
8. MIRROR_WITNESS_SERVER
9. SUSPECT_PAGES

 

I doubt, the user with which probe(5.X) is configured have the required privileges at the table in database, It would be the possible reason that probe is not able to fetch information from table.

 

Note***

There was a bug in probe version older than 5.0 that probe ignores the configured user and use the administrator user by default to establish the connection with the database. So we have observed similar issues in the past where customer complained that probe v4.90 or 4.95 used to work and 5.0 is not working but the actual issue was that the configured user didn’t had the required privileges, so after upgrading to 5.0 probe started using the configured user (which actually doesn’t had the required privileges ) so the required results were not observed.

On giving the required privileges to the configured user, all the customer issues were resolved.

 

In order to confirm the same here in this issue, we need to ask customer to connect to the DB server (SQL server mgmt. studio) using the configured user in probe and then try executing the below checkpoint queries. Based on the outcome we could proceed further with our analysis.

 

av_fragmentation = sp_MSforeachdb 'SELECT distinct ''?'' db_name, OBJECT_NAME(i.object_id) AS tablename, isNull(i.name,'''') AS indexname, phystat.avg_fragmentation_in_percent as fragmentation, alloc_unit_type_desc as alloc_unit FROM sys.dm_db_index_physical_stats(db_id(''?''), NULL, NULL, NULL, ''LIMITED'') phystat JOIN [?].sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id where  OBJECT_NAME(i.object_id) is not null'

 

long_queries = 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

 

mirror_sqlinstance = SELECT rtrim(DB_NAME(database_id)) AS 'object', rtrim(mirroring_partner_instance) AS mirroring_partner_instance FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_role = 1

 

It would be more helpful if customer can open a case for same in order to track it properly.

[Legacy User]

Hi guys. I´ve been opened a case 01133428. Can you confirm if this is a bug ? Does there a oficial workaround ?

 

Thank you.

 

Clecimar

[GregPolenta]

Hi Clecimar,

 

I reviewed the initial case in this post and the problem was found to be a permissions issue. The user specified

   in the database connection was incorrectly specified in this case. It was  DOMAIN.xx.yy\username when it

   should have been DOMAIN\username. The user was verified using the xp_logininfo sql command.The 4.95

   version was not respecting the connection and was passing the user the probe was running as (ex. 

   NT AUTHORITY\SYSTEM ) which likely had access.

 

you may want to verify that the specified user has the necessary permissions to execute the queries in question

 

Regards,

Greg