search

amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.14k stars 281 forks source link

Multiple error when executing sp_whoisactive on SQL Server 2019 #24

Closed aallee1 closed 4 years ago

aallee1 commented 4 years ago

I have the lastest version of sp 11.33. I am runing the proc on SQL Server 2019 CU4 Enterprise version.

I am executing proc with the following parameters: EXEC DBATools.dbo.sp_WhoIsActive @get_plans = 1 , @get_outer_command = 1

If I run a trace on the server while executing sp_whoisactive, I see a number of errors being produced by a single call to the proc.

Invalid object name '#sessions'. Invalid object name '@buffer_results'. Invalid object name '#locks'. Invalid object name '#blocked_requests'.

sp_whoisactive_Trace_Screenshot

amachanic commented 4 years ago

Exceptions are used internally by the query processor for control-of-flow purposes. These are not "real" exceptions.

And you can add this to the list of reasons why you shouldn't be using Profiler. To capture true exceptions in Profiler you need to collect both Exception and User Error message, and then put the two events together. (You can have User Error Message that's not an error, and Exception that's not an exception.)

The Extended Events event, sqlserver.error_reported, consolidates this data in the right way so you see real exceptions, not a bunch of internal "stuff" that doesn't matter.