0

I'm trying to setup a NetScaler (version 9.3) monitor to determine which SQL server my database is running on (principal / mirror). The query I'm using is:

select mirroring_role_desc from sys.database_mirroring where database_id = DB_ID('OperationsManager')

The query works fine running under the user credentials that I created. The problem is, whenever the query comes from the NetScaler it's not completing. In the SQL Profiler trace it's showing an abort / rollback and I have no idea why.

I've enabled literally every trace event, including all the errors, to try to see why it's not completing and I've gotten nowhere. There are no Reads and no RowCounts. The connection appears to be aborting, but not from any sort of error.

This is what profiler looks like when it runs from the NetScaler

This is what it looks like when I run the same query, as the same user, from SSMS.

I thought maybe the NS was closing the connection too early so I upped the response timeout from 2s to 30s. That didn't make any difference. I'm completely baffled.

Matteo
  • 37,680
  • 11
  • 100
  • 115
Levi W
  • 805
  • 6
  • 13

2 Answers2

0

Can you let us know the version of NetScaler code you are running, and paste in the monitor CLI command from the CLI? Also - you need to add a semi colon at the end of your query.

Ronan
  • 1
  • 2
  • NetScaler is version 9.3 and the command we used was: add lb monitor SQL_MIRROR MSSQL-ECV -userName cswxntsp -LRTM DISABLED -database master -sqlQuery "select mirroring_role_desc from sys.database_mirroring where database_id = DB_ID('OperationsManager')" -evalRule "MSSQL.RES.ROW(0).TEXT_ELEM(0).EQ(\"PRINCIPAL\")" – Levi W Jun 12 '13 at 15:55
  • @Ronan this does not qualify as an answer, please reserve questions for comments. – Alec. Jun 12 '13 at 16:14
  • Sorry Alec - havent really posted on this before. – Ronan Jun 13 '13 at 13:35
  • It's fine Ronan, with only 1 reputation you wouldn't be able to add comments to the original post anyways. Any ideas about my issue? – Levi W Jun 13 '13 at 14:12
  • Yes, I edited my comment above as suggested when I was going to provide another answer. Just stick a semi colon at the end of your SQL statement in the monitor and it should work. :-) – Ronan Jun 13 '13 at 14:49
  • Semi-colon did nothing to help. Neither did putting explicit BEGIN TRAN / COMMIT TRAN statements around the select. Nothing seems to stop it from aborting. – Levi W Jun 17 '13 at 16:22
0

Add a semi colon to the end of your SQL statement in the monitor string. Otherwise the statement is not executed.

Ronan
  • 1
  • 2