Got a weird error msg: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

[jtbrown52]

Awesome code, this is super helpful for a project I'm working on.

One thing that came up today was an error msg being returned from vStats view

"STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation."

Looking at the code I figured out I could toss a CAST statement in vStats view to make sure the return came back as NVARCHAR(MAX). I think this happens when there's a large number of columns in a table or the column names are rather long?

select sc.object_id, sc.stats_id, string_agg(CAST(c.name AS NVARCHAR(MAX)), ',') as stat_columns...

I think this may be causing another issue when running the stored procedure StatsOptimize as below:
exec dbo.[StatsOptimize] @Tables=null, @StatisticsModificationLevel=null, @StatisticsSample=null ,@OnlyModifiedStatistics=null,@DeleteOverlappingStats='Y', @TimeLimit=null , @Execute=null

Returns Error:
Arithmetic overflow error converting numeric to data type numeric.

I'm testing this out now in my environment and I'll let you know by responding to this post.

[jtbrown52]

Well the fix above certainly fixed the view, however, the error from the stored proc persists. I'll try to debug this a bit more and open another issue with my findings.

Awesome work again, Bob. This is super helpful.