By default, PI is configured to collect a text of maximum 16300 characters. That means that any text that is larger than that will be cut off. For SQL Server the text is resolved at the batch level, and so the Batch will be cut. However, some statements within the batch may not be visible at all and shown as: SQL text is out of the collected batch configured size.
Increasing the limit for text size is possible. The trade off will be resource allocation - to retrieve more there may be a need to increase the memory and CPU allocated to the PI Agent Manager.
Note: Increasing the maximum size will only affect new SQL Statements and batches detected. It will have no affect on texts already collected.
Execute the script below in order to increase SQL text size allowed by the SQL Server agent in SQL PI. Refer to KB 232352 for details on running a groovy script using the script console in the Foglight Management Server.
This script is for a single database agent due to the performance impact that increasing the text size variable can have when multiple database agents are changed.
Change the two parameters in the script for the required text size and Agent name and then execute the script via the Foglight Script Console.
Note 1: Please put your agent name in line 3:
agentName = "
agentName = "abc123-DefaultInstance";
Note 2: For customers including a parenthesis character "(" or ")" in the agent name, please treat the agent name field as a regular expression. For example: agentName="abcd00001234.example.com.*";
The information in the script provided is known to work successfully; however, it has not been officially tested by Quest Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Support and Development.
Quest Support and Development recommend always making a backup of the current Foglight installation and database prior to execution of any scripts that may modify it.
Enhancement FOM-963 was added beginning with the 188.8.131.52 and higher releases of the SQL Server and Oracle cartridges to provide the capability to increase the batch text size from within the Foglight SQL PI UI. Please review the following video for details on this functionality.