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

• Microsoft SQL Server version 6.5
• Microsoft SQL Server, Enterprise Edition, version 6.5
• Microsoft SQL Server, Small Business Server Edition, version 7.0

SUMMARY

This article discusses a method to trigger the Network Monitor (NetMon) utility to run based on a specific error happening. You can set up SQL Enterprise Manager to respond automatically to SQL Server events, either by executing a task that you have defined or by sending an e-mail and/or a pager message to an operator that you have specified.

MORE INFORMATION

The example included in the procedure below will start the NetMon utility based on the SQL Server logging a user-defined error message. This example will also use the Windows NT AT Scheduler. Because the Scheduler can only be defined with future events, the initial error message may not appear in the capture, but subsequent occurrences will be captured. For additional information, please see the following article in the Microsoft Knowledge Base:

Q192251 : How to Schedule a Task on a Remote Computer

Q148942 : How to Capture Network Traffic with Network Monitor

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

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. '
    

Additional query words: prodsql Trigger Unable to read Login packet

Keywords : SSrvAdmin SSrvErr_Log SSrvStProc
Version : WINDOWS:7.0; WINNT:6.5
Platform : WINDOWS winnt
Issue type : kbinfo