Steve Hebert's Development Blog

I've run into a situation where I want to prevent a SQL TRACE from capturing my SQL script execution.  Encryption/decryption of the script is a separate process not discussed here.

I'm checking the trace status of SQL server after every other script bit (a somewhat arbitrary decision) that's been sent to the server.  If I run into an active trace, I stop running the script and raise an error message stating this condition.  The server is checked every 4 seconds until the user clears the trace or cancels the process.  The code below is from a class that implements threaded processing of the script - this way multiple servers can be upgraded at the same time.  The class is responsible for tracking runtime messages and providing updated progress percentages that are displayed by the UI running on a separate thread (the UI thread periodically queries the object for updated messages and all message processing has been made thread-safe). I'm leaving out the UI pieces and related messaging components of the class because it's just not pertinent to the problem. 

The larger script is broken into pieces by delimiting based on "\ngo\n".

public bool IsTraceRunning(SqlConnection sqlConn)
{
   SqlCommand sqlComm = new SqlCommand("select count(*) from :: fn_trace_getinfo(default)", sqlConn );
   object oObj = sqlComm.ExecuteScalar();
   Int32 iCount = (Int32) sqlComm.ExecuteScalar();
   return iCount > 0;
}

public void ThreadProc()
{
   using( SqlConnection sqlConn = new SqlConnection( _ProcessData.ConnectionString ))
   {
      _ProcessData.AddMessage("Connection string accepted.");
      try
      {
         sqlConn.Open();
         _ProcessData.AddMessage("Connection Opened.");
      }
      catch( Exception ex )
      {
         _ProcessData.AddMessage( string.Format("FATAL ERROR: {0}", ex.ToString()) );
         _ProcessData.FatalError = true;
         return;
      }
      int iPos = 0;
      ArrayList asScriptBits = _ProcessData.UpgradeScripts;
      _ProcessData.AddMessage("Performing Update Scripts...");

      bool bWaitingOnTrace = false;

      while( iPos < asScriptBits.Count && ! _ProcessData.Abort)
      {
         if( iPos % 2 == 0 )
         {
            if( IsTraceRunning(sqlConn))
            {
               if( ! bWaitingOnTrace )
                  _ProcessData.AddMessage("UPGRADE PAUSED! Please terminate all traces against this database to continue.");

               bWaitingOnTrace = true;
               Thread.Sleep(4000); // wait 4 seconds before trying again.
               continue;
            }
         }

         if( bWaitingOnTrace )
         {
            _ProcessData.AddMessage("Trace terminated. Continuing upgrade process.");
            bWaitingOnTrace = false;
         }

         using( SqlCommand sqlComm = new SqlCommand(asScriptBits[iPos] sqlConn ))
         {

            try
            {
               sqlComm.ExecuteNonQuery();
            }
            catch( Exception ex )
            {
               string sErrorCount = string.Format("({0} of {1}): {2}", iPos, asScriptBits.Count, ex.ToString() );
               _ProcessData.AddMessage( ex.ToString() );
               _ProcessData.AddError((string)asScriptBits[iPos], sErrorCount );
            }
         }
         _ProcessData.ProgressCount = iPos + 1;
         iPos++;
      }

      _ProcessData.Finished = true;

      if( _ProcessData.Abort )
         _ProcessData.AddMessage("Process aborted successfully. Upgrade is not complete!");
      else
         _ProcessData.AddMessage("Update Complete.");
   }
}