"SQL Text" for long SQL Statement Text truncated due to excessive length

11/2/2018 7:44 PM

4/15/2025 5:19 PM

DPA will truncate SQL text after a certain length. For the SQL server, historically the length was 7999 characters. As of DPA version 11.0 a support option was added to extend this SQL text length on each server that DPA is monitoring. 
SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH description: "Max length of SQL text fetched from SQL Server 2005+ or Azure SQL DB (max = 49999)."   

Even with this longer parameter, DPA will not recollect text that was collected and truncated before. DPA does the quick poll query to see what is active one time per second. A hash value is created for the SQL statement. If it is determined that DPA does not have the text of the SQL statement for that hash, it will collect it. If DPA does have the SQL text, it does not recollect thing information. SQL statements that were collected and truncated are not recollected even if the longer length parameter is set.

This article details a procedure for setting DPA to collect longer text, and how to try to get it to recollect the SQL with more characters. 

Database Performance Analyzer

Not able to fetch the full SQL text from SQL Server 2005+ or Azure SQL DB (max = 49999).

If they encountered the error, please follow the instructions provided below:

1. In DPA, set the Options menu item to a longer length: Options > Administration tab > Advanced Options > DB Instance Options tab > Support Options check box > Select the SQL server instance from the drop-down menu.
2. Look for SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH and increase this to a value up to 49999.
3. If you want DPA to recollect the SQL text at the new longer length, you have to delete the SQL text from the DPA repository to get the longer text on items that were collected before. If you run "select ID, name from COND", you will see the IDs of the monitored instances. The table for the SQL text would be CONST_ID, where ID is the ID for the server where you have truncated text. You would need to either truncate those CONST_ID tables, or run "DELETE from CONST_ID where H = <hash I need to be recollected at longer length>" 
4. Restart DPA.
5. Wait for the SQL statement to run again on the monitored server.  

Kindly proceed by following the outlined procedure below if the error is not encountered:

1. In DPA, set the Options menu item to a longer length: Options > Administration tab > Advanced Options > DB Instance Options tab > Support Options check box > Select the SQL server instance from the drop-down menu.
2. Look for SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH and increase this to a value up to 49999.
3. Restart DPA.
4. Wait for the SQL statement to run again on the monitored server. 

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.