public static PerformanceData EndTrace(string uri, TraceServer traceServerReader)
{
PerformanceData perf = new PerformanceData(uri);
traceServerReader.Stop();
while (traceServerReader.Read())
{
string ApplicationName = traceServerReader.GetString(2);
if (ApplicationName == null)
{
ApplicationName = string.Empty;
}
if (ApplicationName == "SQL Management")
{
continue;
}
if (ApplicationName == "Microsoft SQL Server Management Studio")
{
continue;
}
if (ApplicationName.StartsWith("SQL Server Profiler"))
{
continue;
}
//0: EventClass
//1: TextData
//2: ApplicationName
//3: NTUserName
//4: LoginName
//5: CPU
//6: Reads
//7: Writes
//8: Duration
//9: ClientProcessID
//10: SPID
//11: StartTime
//12: EndTime
//13: BinaryData
//string query = traceServerReader.GetString(1);
//if (string.IsNullOrEmpty(query)) query = string.Empty;
//if (query.StartsWith("exec sp_executesql N'")) query = query.Remove(0, 21);
// if (query.Length > 64) query = query.Substring(0, 64);
//Console.WriteLine("{0}: {1} -> {2} | CPU={3} | Duration={4}", nEventNum,
// query,
// traceServerReader.GetString(2),
// traceServerReader.GetOrdinal("CPU"),
// traceServerReader.GetOrdinal("Duration"));
perf.Queries++;
object duration = traceServerReader.GetValue(8);
perf.TotalDuration += (duration == null ? 0 : (long)duration);
}
return(perf);
}
private bool ConsoleCtrlCheck(CtrlTypes ctrlType)
{
if (trace != null)
{
Console.WriteLine("Stopping trace ...\n");
trace.Stop();
Console.WriteLine("Closing trace ...\n");
trace.Close();
}
return(true);
}
public static PerformanceData EndTrace(string uri, TraceServer traceServerReader)
{
PerformanceData perf = new PerformanceData(uri);
traceServerReader.Stop();
while (traceServerReader.Read())
{
string ApplicationName = traceServerReader.GetString(2);
if (ApplicationName == null) ApplicationName = string.Empty;
if (ApplicationName == "SQL Management") continue;
if (ApplicationName == "Microsoft SQL Server Management Studio") continue;
if (ApplicationName.StartsWith("SQL Server Profiler")) continue;
//0: EventClass
//1: TextData
//2: ApplicationName
//3: NTUserName
//4: LoginName
//5: CPU
//6: Reads
//7: Writes
//8: Duration
//9: ClientProcessID
//10: SPID
//11: StartTime
//12: EndTime
//13: BinaryData
//string query = traceServerReader.GetString(1);
//if (string.IsNullOrEmpty(query)) query = string.Empty;
//if (query.StartsWith("exec sp_executesql N'")) query = query.Remove(0, 21);
// if (query.Length > 64) query = query.Substring(0, 64);
//Console.WriteLine("{0}: {1} -> {2} | CPU={3} | Duration={4}", nEventNum,
// query,
// traceServerReader.GetString(2),
// traceServerReader.GetOrdinal("CPU"),
// traceServerReader.GetOrdinal("Duration"));
perf.Queries++;
object duration = traceServerReader.GetValue(8);
perf.TotalDuration += (duration == null ? 0 : (long)duration);
}
return perf;
}
private static void ReadTrace(TraceServer trace, CancellationToken token)
{
while (trace.Read() && !token.IsCancellationRequested)
{
var eventClass = trace["EventClass"].ToString();
if (string.Compare(eventClass, "RPC:Completed") == 0)
{
var textData = trace["TextData"].ToString();
if (!textData.Contains("sp_reset_connection") &&
!textData.Contains("sp_trace") &&
!textData.Contains("sqlagent"))
{
eventQueue.Enqueue(textData);
}
}
}
trace.Stop();
trace.Close();
}
void DoWork(object SSAS)
{
string SSASserver = (string)SSAS;
TraceTable _SQLDestTableWriter = null;
TraceServer _SSASSourceTraceServer = null;
int _RetryCounter = 0;
bool bFirstLoop = true;
while (bFirstLoop || _RetryCounter < Properties.Settings.Default.RestartRetries)
{
bFirstLoop = false;
try
{
//Grab connection to SSAS
bool bSuccess = ConnectOlap(out _SSASSourceTraceServer, SSASserver);
if (bSuccess)
{
//Grab connection to SQL and connect it with the SSAS trace
bSuccess = ConnectSQL(ref _SSASSourceTraceServer, out _SQLDestTableWriter, SSASserver);
if (bSuccess)
{
_RetryCounter = 0;
while (_SQLDestTableWriter.Write())
{
if (_SQLDestTableWriter.IsClosed)
{
throw new Exception("SQL connection closed unexpectedly.");
}
if (_SSASSourceTraceServer.IsClosed)
{
throw new Exception("SSAS connection closed unexpectedly.");
}
}
}
}
}
catch (Exception ex)
{
StringBuilder messageText = new StringBuilder();
messageText.Append(DateTime.Now.ToString() + ": Error reading trace: " + ex.Message).AppendLine();
messageText.Append(ex.StackTrace).AppendLine();
while (ex.InnerException != null)
{
messageText.Append("INNER EXCEPTION: ");
messageText.Append(ex.InnerException.Message).AppendLine();
messageText.Append(ex.InnerException.StackTrace).AppendLine();
ex = ex.InnerException;
}
WriteLog(messageText.ToString());
EventLog.WriteEntry(this.ServiceName, messageText.ToString(), EventLogEntryType.Warning);
}
try
{
_SSASSourceTraceServer.Stop();
_SSASSourceTraceServer.Close();
}
catch { }
try
{
_SQLDestTableWriter.Close();
}
catch { }
_RetryCounter++;
if (_RetryCounter < Properties.Settings.Default.RestartRetries)
{
StringBuilder messageText2 = new StringBuilder();
messageText2.Append(DateTime.Now.ToString() + ": Exception caught tracing server: " + SSASserver + ", retry " + _RetryCounter + " of " + Properties.Settings.Default.RestartRetries
+ ". Pausing for " + Properties.Settings.Default.RestartDelayMinutes + " minute(s) then restarting automatically"
).AppendLine();
WriteLog(messageText2.ToString());
EventLog.WriteEntry(this.ServiceName, messageText2.ToString(), EventLogEntryType.Warning);
System.Threading.Thread.Sleep(new TimeSpan(0, Properties.Settings.Default.RestartDelayMinutes, 0));
}
else
{
WriteLog(DateTime.Now.ToString() + ": Exceeded the number of allowed retries for server: " + SSASserver);
}
}
//if this one trace exceeded the number of retries so stop the service and stop all traces
Stop();
}