![]() ![]() 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". You could add a statement like this to the procedure: INSERT debugtable(value) VALUES(locktimeout) to see what lock timeout its running with. However, it did not stop these messages from being recorded Yet then again, if the locks are short-lived and the locktimeout is set to 0, it should help with unsetting the locktimeout. 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. It wont let me create a table with a SQL statement. That would not otherwise see the light of day because it is not really an error condition. The first thing is to run a transaction without adding commit transaction, for example: Second, modify the SET LOCK TIMEOUT in SQL Server Management Studio->Tools->Options->Query Execution->SQL Server->Advanced change the SET LOCK TIMEOUT from -1 to 10000 miliseconds. 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. Once it errors out, I use TSQL to gather any information I need.įor more information, please refer to this MSDN article.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. ![]() This way, it will wait longer before times-out. Change it to 60 or 90 seconds (as you see fit). On the right had side, look for ‘ Override connection string time-out value for table designer updates: Transaction time-out after: 30 seconds‘. With in SSMS, under Options menu, go to, ‘ Designers‘ > ‘ Table and Database Designers‘. Surprisingly, if you query the catalog using TSQL, it works. After a few seconds, it came back with this error message. ![]() So, when we clicked on the tree structure in SSMS, there was no response. Since this was a large table, the ALTER TABLE took more than a few minutes and for the whole duration of this change the catalog would be locked. That makes sense, as we are attempting to change the table definition which is in the catalog. During this time, looks like, the database catalog is locked. One of our deployment steps changes the table definition of a large table. After the event passed, and everyone came back to normal state, it became apparent that this is a innocuous error. This nebulous error has caused some confusion in the team, as it occurred during a high pressure situation. A few weeks ago, we ran into an interesting error. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |