Find the program name and login name which was running the sql

SQLAdm1n

Hi there,
by using the CLR and code similar to the one below, I have managed to retrieve the TOP QUERIES (the same as in the RedGate Monitor tool) but I am having a hard time joining this to any other view or table to retrieve the program name and login name.

Do you know where this data is saved and how to join it to [data].[Cluster_SqlServer_TopQueries_UnstableSamples]?

Any help would be much appreciated.

https://productsupport.red-gate.com/hc/en-us/articles/360009784854-How-to-view-queries-stored-in-the-SQL-Monitor-data-repository
https://forum.red-gate.com/discussion/19811/sql-monitor-query-one-of-the-top-10-expensive-queries

Eddie D

Hi,

Thank you for your forum post.

The underlying schema for the SQL Monitor Repository database is liable to change without any notice.  Therefore we do not support any direct querying of the SQL Monitor repository database.

Also the actual layout of the schema is not published in any documentation I can access, this means I do not know the objects in the database and the dependencies between them.

SQLAdm1n

Hi Eddie,

Thanks for picking up this post.

I do understand the consequences and that it is not supported, but sometimes the login name and program name are not filled in GUI. That's why I wanted to search for the data from the repository DB.

Without the login and program name, I cannot identify which application was hammering the DB (without using XE, DMVs, QS... or any other tool).

Would be great if we could get this data.

Eddie D

Hi, thank you for your reply.

Not sure if this helps, as I am at the limit of my knowledge of the schema for the SQL Monitor repository.

The table [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_StableSamples] includes two columns [_LoginName] and [_ProgramName].

It may then be possible to join on the [Id] and [CollectionDate] columns when creating your view.

I recommend you take a backup of the repository database,  then restore to a new database and test your queries against the restored version of the repository.

Many Thanks
Eddie

SQLAdm1n

Hi Eddie,

I tried doing this a few months ago, but no luck seems something more needs to be used to get meaningful data.

Best regards,

Vuk