INF: How to Run NetMon Based on a Specific Error Occurring

This article was previously published under Q198473
This article has been archived. It is offered "as is" and will no longer be updated.
This article discusses a method to trigger the Network Monitor (NetMon)utility to run based on a specific error happening. You can set up SQLEnterprise Manager to respond automatically to SQL Server events, either byexecuting a task that you have defined or by sending an e-mail and/or apager message to an operator that you have specified.
More information
The example included in the procedure below will start the NetMon utilitybased on the SQL Server logging a user-defined error message. This examplewill also use the Windows NT AT Scheduler. Because the Scheduler can onlybe defined with future events, the initial error message may not appear inthe capture, but subsequent occurrences will be captured.

The example included in the procedure below requires the Network Monitortool to be installed on the same computer as SQL Server. NetMon willcapture all network traffic between specific computers. For additionalinformation about how to install NetMon or read NetMon traces, please seethe following article in the Microsoft Knowledge Base:
148942 : How to Capture Network Traffic with Network Monitor

How to automate running NetMon.exe based on a user defined error message

The example below is useful in capturing events that occur with a highfrequency. To automate NetMon, perform the following steps. If you needadditional information about how to accomplish any of the tasks below,refer to the appropriate topic in SQL Server Books Online.
  1. Create a new alert and supply the following values:
          Name: My Test alert      Severity: 016 - Miscellaneous User Error      Error Message Contains this text: << Text of message >>      Tasks to Execute: Startup Netmon      Optional: Alert Notification Message to send to Operator: "User      Errors are occurring" 						
  2. Create a new task and supply the following values:
          Name: Startup Netmon      Type: TSQL      Database: your_database_here      Command: exec RunNetmon      Options: Write to the Windows NT application event log on failure      NOTE: This check box is available in the New Task dialog box. 						
  3. Create a table with the following structure:
          CREATE TABLE dbo.TempTime      (Sched_Time char (5) NOT NULL ,      Last_run datetime NOT NULL) 						
  4. Create a stored procedure called "RunNetmon" using this code:
         create procedure RunNetmon   as   -- We may only want this stored procedure to run once on demand.   -- If the error occurs frequently the following stored procedure will   -- only allow it to run once in a day. Otherwise, this sample will start   -- multiple sessions of Netmon.exe on the server.   if  (select count(*) from TempTime   where convert(char(8),Last_run,3)  = convert(char(8),getdate(),3)  ) = 0   begin   print'table is empty. Inserting row...'   insert into TempTime (Sched_Time, Last_run) values (   convert(char(2),datepart(hour,getdate() )) +":"  +   convert(char(2),datepart(minute,getdate()) +1) ,getdate() )   -- Adds 1 minute to the current time for the scheduled time.   declare @mytime1 char(32)   declare @mystring varchar(255)   select @mytime1 =  (select Sched_Time from TempTime)   -- Compose the scheduled task from NT AT scheduler   select @mystring = "AT " + @mytime1 +   " /interactive c:\winnt\system32\netmon\netmon.exe /buffersize:15728640   /autostart"   -- Load AT scheduler   exec master..xp_cmdshell @mystring   -- Display the current scheduled events   exec master..xp_cmdshell "AT"   end   else   print 'This stored procedure has already run today. '						
prodsql Trigger Unable to read Login packet

Article ID: 198473 - Last Review: 10/26/2013 17:51:00 - Revision: 4.0

  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.5 Enterprise Edition
  • Microsoft SQL Server 7.0 Small Business Server Edition
  • kbnosurvey kbarchive kbinfo KB198473