![]() I am afraid we may need to stop using event notifications for exceptions due to this problem, because it is filling up the table used to capture the messages at a faster rate than they can be deleted. There is definitely no problem experienced by the processes that seem to generate or at least trigger these messages. The volume is especially high during the time the batch job is running but there are also timeout messages recorded throughout theĭay. It is occurring for us on six different servers. I am surprised no one else seems to have encountered this problem. I tried the "fan_in" option on the event notification, hoping that it might at least reduce the volume of these messages. They are do not have a status of "background" but rather of "sleeping". The SPID's that get recorded with these messages are performing a command called "task manager". I ran the trace and found that it failed due to 'lock escalation' event. Msg 1222, Level 16, State 55, Server XXXXXX, Procedure MyStoredPROC, Line 165 Lock request time out period exceeded. However, it did not stop these messages from being recorded Warning: Null value is eliminated by an aggregate or other SET operation. It did stop the timeout messages from being recorded in the SQL Server Log, as it said it would. Pretty? Well, deferred name resolution is one of the biggest misfeatures in SQL Server in my opinion.Įrland Sommarskog, SQL Server MVP, on this: I did install SQL Server 2012 SP2 as suggested by the Microsoft Support article above. If you try this again, but uncomment the CREATE TABLE statement, you again get two Error:Exception events, but this time you don't get the third and the User Message event. Only first when the statement is reached you get both the Exception event and the User Message event. When you invoke the procedure, the procedure is compiled and put into cache, the exception occurs anew, but is again suppresed. That is, you get one exception event when the procedure is created, but the error is suppressed in the spirit of deferred name resolution. You can see this also when you run Profiler and capture the Error:Exception and Error:User Message events, as well as the BatchStarting/Completed and StmtStarting/Completed events.Įrror:Exception Invalid Object Name '#temp'Įrror:UserMessage Invalid Object Name '#temp' I strongly suspect that is the case with these timeout messages as well. That would not otherwise see the light of day because it is not really an error condition. ![]() It seems like it is picking up some kind of low-level information ![]() For example, afterĮxecuting sp_help I get a series of "errors" saying "Invalid object name '#spcnsttab'" and "Invalid object name '#spindtab'". It constantly records messages saying that temporary tables are not valid objects. One other note about event notification for exceptions: I know that at least one of the other types of message it captures is also bogus.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |