private void ExecuteProfilerAttachSQL(SqlCommand cmd)
{
string mdfPath = m_analysisPath.EndsWith(".mdf") ?
m_analysisPath.Substring(0, m_analysisPath.LastIndexOf("\\") + 1) :
m_analysisPath.EndsWith(".trc") ?
mdfPath = m_analysisPath.Substring(0, m_analysisPath.LastIndexOf("\\") + 1) + "Analysis\\" :
m_analysisPath + "\\Analysis\\";
cmd = new SqlCommand("SELECT COUNT(*) FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "'", connSqlDb);
if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
{
// The database was already attached - change default to leave as it was before.
chkDettachProfilerAnalysisDBWhenDone.Invoke(new System.Action(() => chkDettachProfilerAnalysisDBWhenDone.Checked = false));
}
else
{
chkDettachProfilerAnalysisDBWhenDone.Invoke(new System.Action(() => chkDettachProfilerAnalysisDBWhenDone.Checked = true));
}
cmd = new SqlCommand("IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "') CREATE DATABASE [" + AnalysisTraceID + "] ON (FILENAME = N'" + mdfPath + AnalysisTraceID + ".mdf'),"
+ "(FILENAME = N'" + mdfPath + AnalysisTraceID + ".ldf') "
+ "FOR ATTACH", connSqlDb);
cmd.ExecuteNonQuery();
bProfilerTraceDbAttached = true;
Invoke(new System.Action(() =>
{
splitProfilerAnalysis.Visible = true;
ttStatus.SetToolTip(chkDettachProfilerAnalysisDBWhenDone, "Profiler traces were imported into a trace database in the file:\r\n" + AnalysisTraceID
+ ".mdf\r\n\r\nLocated at:\r\n" + mdfPath + "\\Analysis\r\n\r\n"
+ "Uncheck this checkbox if the scenario requires further analysis offline, to leave the database attached when exiting this tool.\r\n\r\n"
+ "NOTE: While attached the SQL data source at [" + (connSqlDb.DataSource.StartsWith(".") ? Environment.MachineName + connSqlDb.DataSource.Substring(1) : connSqlDb.DataSource) + "] locks these files from deletion.");
ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Attached trace database [" + AnalysisTraceID + "]\r\nto SQL instance [" + (connSqlDb.DataSource.StartsWith(".") ? Environment.MachineName + connSqlDb.DataSource.Substring(1) : connSqlDb.DataSource) + "]\r\nfor analysis at " + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss UTCzzz") + ".\r\n");
}));
}
private void AttachProfilerTraceDB()
{
splitProfilerAnalysis.Invoke(new System.Action(() => splitProfilerAnalysis.Visible = splitProfilerAnalysis.Enabled = btnAnalysisFolder.Enabled = false));
tcAnalysis.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text += (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") || ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + "Attaching profiler trace database..."));
ValidateProfilerTraceDBConnectionStatus();
if (connSqlDb.State == ConnectionState.Open)
{
connSqlDb.ChangeDatabase("master");
SqlCommand cmd = new SqlCommand("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "') ALTER DATABASE [" + AnalysisTraceID + "] SET MULTI_USER", connSqlDb);
cmd.ExecuteNonQuery();
try { ExecuteProfilerAttachSQL(cmd); }
catch (SqlException ex)
{
LogException(ex);
Invoke(new System.Action(() => cmbProfilerAnalyses.Enabled = txtProfilerAnalysisQuery.Enabled = false));
if (ex.Message.Contains("cannot be opened because it is version"))
{
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
while (true)
{
if (sqlprompt.ShowDialog(this) == DialogResult.OK)
{
Properties.Settings.Default["SqlForProfilerTraceAnalysis"] = sqlprompt.cmbServer.Text;
Properties.Settings.Default.Save();
connSqlDb = new SqlConnection("Data Source=" + sqlprompt.cmbServer.Text + ";Integrated Security=true;Persist Security Info=false;");
connSqlDb.Open();
cmd.Connection = connSqlDb;
try
{
ExecuteProfilerAttachSQL(cmd);
break;
}
catch (Exception ex2)
{
LogException(ex2);
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to load trace data to SQL table. No local instance able to host the data is available.\r\nTry opening again on a machine with a local SQL instance running.")));
return;
}
}
}
else if (ex.Message.Contains("Unable to open the physical file") || ex.Message.Contains("The path specified by"))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n"));
return;
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to attach to database due to exception:\r\n" + ex.Message)));
}
}
}
btnAnalysisFolder.Invoke(new System.Action(() => btnAnalysisFolder.Enabled = splitProfilerAnalysis.Enabled = true));
}
private bool ValidateProfilerTraceDBConnectionStatus()
{
if (connSqlDb.State != ConnectionState.Open)
{
string sqlForTraces = Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").GetValue("SqlForProfilerTraceAnalysis") as string;
string exMsg = "";
if (sqlForTraces != "")
{
connSqlDb = new SqlConnection("Data Source=" + sqlForTraces + ";Integrated Security=true;Connection Timeout=2;");
try
{
connSqlDb.Open();
}
catch (Exception ex)
{
if (!ex.Message.StartsWith("A network-related or instance-specific error occurred while establishing a connection to SQL Server."))
{
LogException(ex);
}
sqlForTraces = "";
exMsg = ex.Message;
}
}
if (sqlForTraces == "")
{
DialogResult r = DialogResult.Abort;
Invoke(new System.Action(() =>
{
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
sqlprompt.cmbServer.Text = sqlForTraces;
r = sqlprompt.ShowDialog(this);
if (r == DialogResult.OK)
{
Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").SetValue("SqlForProfilerTraceAnalysis", sqlprompt.cmbServer.Text);
sqlForTraces = sqlprompt.cmbServer.Text;
}
}));
if (r == DialogResult.OK)
{
connSqlDb = new SqlConnection("Data Source=" + Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").GetValue("SqlForProfilerTraceAnalysis") as string + ";Integrated Security=true;Persist Security Info=false;");
try { connSqlDb.Open(); }
catch (Exception ex) { LogException(ex); }
}
else
{
Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Failure attaching to trace database: " + exMsg + "\r\n");
btnImportProfilerTrace.Visible = true;
}));
return(false);
}
}
}
return(true);
}
private void ValidateProfilerTraceViews()
{
SqlConnection conn = new SqlConnection(connSqlDb.ConnectionString.Replace("Connection Timeout=2;", ""));
conn.Open();
conn.ChangeDatabase(AnalysisTraceID);
SqlCommand cmd = new SqlCommand("SELECT TOP 1 name FROM sys.views WHERE name = N'" + AnalysisTraceID + "_v'", conn);
if (cmd.ExecuteScalar() != null)
{
bProfilerEventClassSublcassViewPresent = true;
}
else
{
bProfilerEventClassSublcassViewPresent = false;
}
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Confirmed event class/subclass view is " + (bProfilerEventClassSublcassViewPresent ? "present." : "not present."))));
cmd.CommandText =
@"
declare @sql nvarchar(max) =
(case when exists (select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '" + AnalysisTraceID + @"' and
COLUMN_NAME = 'currentTime' -- and schema name too, if you like
)
then 'select max(currentTime) from [" + AnalysisTraceID + @"]'
else 'select NULL as currentTime'
end)
;
exec sp_executesql @sql;
";
new Thread(new ThreadStart(() =>
{
if (cmd.Connection.State == ConnectionState.Open)
{
// This isn't immediately required and we save 1-2s by doing it off this thread.
EndOfTrace = DateTime.MinValue;
object dt = cmd.ExecuteScalar();
if (!Convert.IsDBNull(dt))
{
EndOfTrace = Convert.ToDateTime(dt);
System.Diagnostics.Trace.WriteLine(Program.CurrentFormattedLocalDateTime() + ": End of trace [" + AnalysisTraceID + "] noted at " + EndOfTrace);
}
conn.Close();
}
})).Start();
Invoke(new System.Action(() =>
{
cmbProfilerAnalyses.DataSource = ProfilerTraceAnalysisQueries.Where(q => q.QueryType == (bProfilerEventClassSublcassViewPresent ? ProfilerQueryTypes.QueriesWithEventClassSubclassNames :
ProfilerQueryTypes.BaseQuery) ||
q.Name == "").ToList();
cmbProfilerAnalyses.Refresh();
}));
}
private void BgCancelTrace_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
ProfilerTraceStatusTextBox.AppendText("\r\nDropped trace database and deleted files successfully.\r\nTrace file is not yet imported to database table for analysis. Import to perform analysis.\r\n");
AnalysisMessagePumpTimer.Stop();
tbCollection.ForeColor = SystemColors.ControlText;
tcCollectionAnalysisTabs.Refresh();
tbCollection.Enabled = true;
btnAnalysisFolder.Enabled = true;
btnImportProfilerTrace.Enabled = true;
btnImportProfilerTrace.Text = "Import and &Analyze";
}
private void AnalysisMessagePumpTimer_Tick(object sender, EventArgs e)
{
if (tcAnalysis.SelectedTab.Name == "tbProfilerTraces")
{
ProfilerTraceStatusTextBox.AppendText(".");
}
if (tcAnalysis.SelectedTab.Name == "Network Trace")
{
(tcAnalysis.SelectedTab.Controls["StatusTextBox"] as TextBox).AppendText(".");
}
}
private bool ValidateProfilerTraceDBConnectionStatus()
{
if (connSqlDb.State != ConnectionState.Open)
{
string sqlForTraces = Properties.Settings.Default["SqlForProfilerTraceAnalysis"] as string;
string exMsg = "";
if (sqlForTraces != "")
{
connSqlDb = new SqlConnection("Data Source=" + sqlForTraces + ";Integrated Security=true;Connection Timeout=2;");
try
{
connSqlDb.Open();
}
catch (Exception ex)
{
LogException(ex);
sqlForTraces = "";
exMsg = ex.Message;
}
}
if (sqlForTraces == "")
{
DialogResult r = DialogResult.Abort;
Invoke(new System.Action(() =>
{
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
sqlprompt.cmbServer.Text = sqlForTraces;
r = sqlprompt.ShowDialog(this);
if (r == DialogResult.OK)
{
Properties.Settings.Default["SqlForProfilerTraceAnalysis"] = sqlForTraces = sqlprompt.cmbServer.Text;
Properties.Settings.Default.Save();
}
}));
if (r == DialogResult.OK)
{
connSqlDb = new SqlConnection("Data Source=" + Properties.Settings.Default["SqlForProfilerTraceAnalysis"] + ";Integrated Security=true;Persist Security Info=false;");
try { connSqlDb.Open(); }
catch (Exception ex) { LogException(ex); }
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Failure attaching to trace database: " + exMsg + "\r\n")));
return(false);
}
}
}
return(true);
}
private void ValidateProfilerTraceViews()
{
SqlConnection conn = new SqlConnection(connSqlDb.ConnectionString.Replace("Connection Timeout=2;", ""));
conn.Open();
conn.ChangeDatabase(AnalysisTraceID);
SqlCommand cmd = new SqlCommand("SELECT TOP 1 name FROM sys.views WHERE name = N'" + AnalysisTraceID + "_v'", conn);
if (cmd.ExecuteScalar() != null)
{
bProfilerEventClassSublcassViewPresent = true;
}
else
{
bProfilerEventClassSublcassViewPresent = false;
}
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Confirmed event class/subclass view is " + (bProfilerEventClassSublcassViewPresent ? "present." : "not present."))));
cmd.CommandText = "SELECT MAX(CurrentTime) FROM [" + AnalysisTraceID + "]";
new Thread(new ThreadStart(() =>
{
if (cmd.Connection.State == ConnectionState.Open)
{
// This isn't immediately required and we save 1-2s by doing it off this thread.
EndOfTrace = Convert.ToDateTime(cmd.ExecuteScalar());
System.Diagnostics.Trace.WriteLine("End of trace [" + AnalysisTraceID + "] noted at " + EndOfTrace);
conn.Close();
}
})).Start();
Invoke(new System.Action(() =>
{
cmbProfilerAnalyses.DataSource = ProfilerTraceAnalysisQueries.Where(q => q.QueryType == (bProfilerEventClassSublcassViewPresent ? ProfilerQueryTypes.QueriesWithEventClassSubclassNames :
ProfilerQueryTypes.BaseQuery) ||
q.Name == "").ToList();
cmbProfilerAnalyses.Refresh();
}));
}
private void btnImportProfilerTrace_Click(object sender, EventArgs e)
{
if (btnImportProfilerTrace.Text == "Import and &Analyze")
{
bCancelProfilerImport = false;
tbCollection.ForeColor = SystemColors.ControlDark;
tcCollectionAnalysisTabs.Refresh();
tbCollection.Enabled = false;
btnAnalysisFolder.Enabled = false;
btnImportProfilerTrace.Text = "&Cancel Import";
LogFeatureUse("Profiler Analysis", "Started");
if (!ValidateProfilerTraceDBConnectionStatus())
{
tbCollection.ForeColor = SystemColors.ControlText;
tcCollectionAnalysisTabs.Refresh();
tbCollection.Enabled = true;
btnAnalysisFolder.Enabled = true;
btnImportProfilerTrace.Enabled = true;
btnImportProfilerTrace.Text = "Import and &Analyze";
return;
}
AnalysisMessagePumpTimer.Interval = 1000;
AnalysisMessagePumpTimer.Start();
try
{
BackgroundWorker bg = new BackgroundWorker();
bg.DoWork += bgImportProfilerTrace;
bg.RunWorkerCompleted += bgImportProfilerTraceComplete;
bg.RunWorkerAsync(new object[] { ProfilerTraceStatusTextBox, "Initial Catalog=" + AnalysisTraceID + ";Persist Security Info=False;" + connSqlDb.ConnectionString });
}
catch (SqlException ex)
{
LogException(ex);
if (ex.Message == "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.")
{
ProfilerTraceStatusTextBox.Text = "Trace file is not yet imported to database table for analysis. No SQL Server was available to perform import.";
btnImportProfilerTrace.Visible = true;
}
else
{
ProfilerTraceStatusTextBox.Text = "Error loading profiler trace: \r\n" + ex.Message;
}
ProfilerTraceStatusTextBox.AppendText("\r\nImport trace to database failed. Dropping trace database...");
LogFeatureUse("Profiler Analysis", "Failed: " + ex.Message);
BackgroundWorker bgCancelTrace = new BackgroundWorker();
bgCancelTrace.DoWork += BgCancelTrace_DoWork;
bgCancelTrace.RunWorkerCompleted += BgCancelTrace_RunWorkerCompleted;
bgCancelTrace.RunWorkerAsync();
}
}
else
{
bCancelProfilerImport = true;
btnImportProfilerTrace.Enabled = false;
ProfilerTraceStatusTextBox.AppendText("\r\nUser cancelled loading of trace to table. Dropping trace database...");
LogFeatureUse("Profiler Analysis", "User cancelled import");
BackgroundWorker bgCancelTrace = new BackgroundWorker();
bgCancelTrace.DoWork += BgCancelTrace_DoWork;
bgCancelTrace.RunWorkerCompleted += BgCancelTrace_RunWorkerCompleted;
bgCancelTrace.RunWorkerAsync();
}
}
private void bgImportProfilerTrace(object sender, DoWorkEventArgs e)
{
try
{
if (m_analysisPath.EndsWith(".trc"))
{
m_analysisPath = m_analysisPath.Substring(0, m_analysisPath.LastIndexOf("\\") + 1);
}
SetAnalysisFolderPermissionsAndCreateDB(m_analysisPath);
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
ProfilerTraceStatusTextBox.Text = "Importing profiler trace to database [" + AnalysisTraceID + "] on SQL instance: [" + (connSqlDb.DataSource == "." ? Environment.MachineName : connSqlDb.DataSource) + "]."));
string connstr = (e.Argument as object[])[1] as string;
if (!connstr.Contains("Initial Catalog"))
{
connstr += (connstr.EndsWith(";") ? "" : ";") + "Initial Catalog=" + AnalysisTraceID + ";";
}
if (connstr.Contains("Initial Catalog=;"))
{
connstr = connstr.Replace("Initial Catalog=;", "Initial Catalog=" + AnalysisTraceID + ";");
}
ASProfilerTraceImporterProcess = new Process();
ASProfilerTraceImporterProcess.StartInfo.UseShellExecute = false;
ASProfilerTraceImporterProcess.StartInfo.CreateNoWindow = true;
ASProfilerTraceImporterProcess.StartInfo.RedirectStandardOutput = true;
ASProfilerTraceImporterProcess.StartInfo.FileName = Program.TempPath + "ASProfilerTraceImporterCmd.exe";
ASProfilerTraceImporterProcess.StartInfo.Arguments = "\"" + Directory.GetFiles(m_analysisPath, AnalysisTraceID + "*.trc")[0] + "\" \"" + connstr + "\" \"" + AnalysisTraceID + "\"";
ASProfilerTraceImporterProcess.Start();
while (!ASProfilerTraceImporterProcess.HasExited)
{
string sOut = ASProfilerTraceImporterProcess.StandardOutput.ReadLine();
System.Diagnostics.Trace.WriteLine(sOut);
if (sOut != null)
{
if (sOut.StartsWith("Loaded "))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
{
List <string> loadedline = ProfilerTraceStatusTextBox.Lines.Where(l => l.StartsWith("Loaded ")).ToList();
if (loadedline.Count > 0)
{
ProfilerTraceStatusTextBox.Text = ProfilerTraceStatusTextBox.Text.Replace(loadedline.First(), sOut);
}
else
{
ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + sOut);
}
}
));
}
else if (sOut == "Database prepared for analysis." || sOut == "Import of profiler trace cancelled.")
{
break;
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + sOut)));
}
}
}
if (!bCancelProfilerImport)
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Adding profiler database to collection data...")));
DettachProfilerTraceDB(false);
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".ldf");
if (txtFolderZipForAnalysis.Text.EndsWith(".zip"))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText("\r\nDeleting redundant profiler trace files from data extraction location...")));
foreach (string file in Directory.EnumerateFiles(m_analysisPath, AnalysisTraceID + "*.trc"))
{
File.Delete(file);
}
}
AttachProfilerTraceDB();
}
}
catch (Exception ex)
{
LogException(ex);
if (ex.Message == "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.")
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text = "Trace file is not yet imported to database table for analysis. No SQL Server was available to perform import.\r\n"));
btnImportProfilerTrace.Invoke(new System.Action(() => btnImportProfilerTrace.Visible = true));
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text = "Error loading trace: " + ex.Message + "\r\n"));
}
}
}
private void AttachProfilerTraceDB()
{
CurrentProfilerTraceColumnList = new List <string>();
splitProfilerAnalysis.Invoke(new System.Action(() => splitProfilerAnalysis.Visible = splitProfilerAnalysis.Enabled = btnAnalysisFolder.Enabled = false));
tcAnalysis.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.Text += (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") || ProfilerTraceStatusTextBox.Text == "" ? "" : "\r\n") + "Attaching profiler trace database..."));
ValidateProfilerTraceDBConnectionStatus();
if (connSqlDb.State == ConnectionState.Open)
{
connSqlDb.ChangeDatabase("master");
SqlCommand cmd = new SqlCommand("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + AnalysisTraceID + "') ALTER DATABASE [" + AnalysisTraceID + "] SET MULTI_USER", connSqlDb);
cmd.ExecuteNonQuery();
try {
ExecuteProfilerAttachSQL(cmd);
cmd.CommandText = "select column_name from [" + AnalysisTraceID + "].INFORMATION_SCHEMA.COLUMNS where table_name = N'" + AnalysisTraceID + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CurrentProfilerTraceColumnList.Add((dr["column_name"] as string).ToLower());
}
dr.Close();
}
catch (SqlException ex)
{
LogException(ex);
Invoke(new System.Action(() => cmbProfilerAnalyses.Enabled = txtProfilerAnalysisQuery.Enabled = false));
if (ex.Message.Contains("cannot be opened because it is version"))
{
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
frmSimpleSQLServerPrompt sqlprompt = new frmSimpleSQLServerPrompt();
while (true)
{
if (sqlprompt.ShowDialog(this) == DialogResult.OK)
{
Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").SetValue("SqlForProfilerTraceAnalysis", sqlprompt.cmbServer.Text);
connSqlDb = new SqlConnection("Data Source=" + sqlprompt.cmbServer.Text + ";Integrated Security=true;Persist Security Info=false;");
connSqlDb.Open();
cmd.Connection = connSqlDb;
try
{
ExecuteProfilerAttachSQL(cmd);
cmd.CommandText = "select column_name from [" + AnalysisTraceID + "].INFORMATION_SCHEMA.COLUMNS where table_name = N'" + AnalysisTraceID + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CurrentProfilerTraceColumnList.Add((dr["column_name"] as string).ToLower());
}
dr.Close();
break;
}
catch (Exception ex2)
{
LogException(ex2);
MessageBox.Show("Unable to attach to database since it was created with a later version of SQL than the selected server.", "Select another instance", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to load trace data to SQL table. No local instance able to host the data is available.\r\nTry opening again on a machine with a local SQL instance running.")));
return;
}
}
}
else if (ex.Message.Contains("Unable to open the physical file") || ex.Message.Contains("The path specified by"))
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") && !String.IsNullOrWhiteSpace(ProfilerTraceStatusTextBox.Text) ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n";
btnImportProfilerTrace.Visible = true;
}));
return;
}
else
{
ProfilerTraceStatusTextBox.Invoke(new System.Action(() => ProfilerTraceStatusTextBox.AppendText((ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") ? "" : "\r\n") + "Unable to attach to database due to exception:\r\n" + ex.Message)));
ProfilerTraceStatusTextBox.Invoke(new System.Action(() =>
{
ProfilerTraceStatusTextBox.Text = (ProfilerTraceStatusTextBox.Text.EndsWith("\r\n") && !String.IsNullOrWhiteSpace(ProfilerTraceStatusTextBox.Text) ? "" : "\r\n") + "Trace file is not yet imported to database table for analysis. Import to perform analysis.\r\n";
btnImportProfilerTrace.Visible = true;
}));
}
}
}
btnAnalysisFolder.Invoke(new System.Action(() => btnAnalysisFolder.Enabled = splitProfilerAnalysis.Enabled = true));
}
private void PopulateAnalysisTabs()
{
lblInitialAnalysisPrompt.Visible = false;
if (connSqlDb.State != ConnectionState.Closed)
{
connSqlDb.Close();
}
if (File.Exists(m_analysisPath))
{
if (!m_analysisPath.EndsWith(".zip"))
{
AnalysisTraceID = m_analysisPath.Substring(0, m_analysisPath.LastIndexOf(".")).Substring(m_analysisPath.LastIndexOf("\\") + 1).TrimEnd("0123456789".ToCharArray());
}
}
else
{
AnalysisTraceID = GetAnalysisIDFromLog();
}
foreach (TabPage t in tcAnalysis.TabPages)
{
if (t.Name == "tbProfilerTraces")
{
HiddenTabPages.Add(t);
}
}
tcAnalysis.TabPages.Clear();
btnImportProfilerTrace.Visible = true;
string mdfPath = "";
if (m_analysisPath != null)
{
if (m_analysisPath.EndsWith(".zip"))
{
SelectivelyExtractAnalysisDataFromZip();
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith("\\msmdsrv.ini")) || File.Exists(m_analysisPath + "\\msmdsrv.ini"))
{
tcAnalysis.TabPages.Add(new TabPage("Configuration")
{
ImageIndex = 0, Name = "Configuration"
});
tcAnalysis.TabPages["Configuration"].Controls.Add(GetStatusTextBox("Check back soon for automated analysis of configuration details."));
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".mdmp")) ||
(!File.Exists(m_analysisPath) && Directory.GetFiles(m_analysisPath, "*.mdmp", SearchOption.AllDirectories).Count() > 0))
{
if (ValidateProfilerTraceDBConnectionStatus())
{
ucASDumpAnalyzer DumpAnalyzer = new ucASDumpAnalyzer(m_analysisPath, connSqlDb);
DumpAnalyzer.Dock = DockStyle.Fill;
tcAnalysis.TabPages.Add(new TabPage("Memory Dumps")
{
ImageIndex = 1, Name = "Memory Dumps"
});
tcAnalysis.TabPages["Memory Dumps"].Controls.Add(DumpAnalyzer);
}
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".evtx")) ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + "_Application.evtx") ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + "_System.evtx"))
{
tcAnalysis.TabPages.Add(new TabPage("Event Logs")
{
ImageIndex = 2, Name = "Event Logs"
});
tcAnalysis.TabPages["Event Logs"].Controls.Add(GetStatusTextBox("Check back soon for automated analysis of event logs."));
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".etl")) ||
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".cap")) ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + ".etl") ||
File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.diag.log"))
{
tcAnalysis.TabPages.Add(new TabPage("Network Trace")
{
ImageIndex = 3, Name = "Network Trace"
});
TextBox txtNetworkAnalysis = GetStatusTextBox();
Button btnAnalyzeNetworkTrace = new Button()
{
Text = "Analyze Trace", Name = "btnAnalyzeNetworkTrace", Left = tcAnalysis.Width / 2 - 54, Width = 108, Top = 80, Visible = false
};
tcAnalysis.TabPages["Network Trace"].Controls.Add(btnAnalyzeNetworkTrace);
btnAnalyzeNetworkTrace.Click += btnAnalyzeNetworkTrace_Click;
tcAnalysis.TabPages["Network Trace"].Controls.Add(txtNetworkAnalysis);
if (File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log"))
{
txtNetworkAnalysis.Text = File.ReadAllText(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.diag.log");
}
else
{
btnAnalyzeNetworkTrace.Visible = true;
}
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".blg")) || File.Exists(m_analysisPath + "\\" + AnalysisTraceID + ".blg"))
{
tcAnalysis.TabPages.Add(new TabPage("Performance Logs")
{
ImageIndex = 4, Name = "Performance Logs"
});
tcAnalysis.TabPages["Performance Logs"].Controls.Add(GetStatusTextBox("Check back soon for automated analysis of performance logs."));
}
if (
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".trc")) ||
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".mdf")) ||
(
!File.Exists(m_analysisPath) &&
(Directory.GetFiles(m_analysisPath, AnalysisTraceID + "*.trc").Count() > 0 ||
File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf"))
)
)
{
SetupSQLTextbox();
LogFeatureUse("Profiler Analysis", "Initializing analysis tab");
splitProfilerAnalysis.Visible = false;
ProfilerTraceStatusTextBox.Text = "";
tcAnalysis.TabPages.Add(HiddenTabPages.Where(t => t.Text == "Profiler Traces").First());
HiddenTabPages.Remove(HiddenTabPages.Where(t => t.Text == "Profiler Traces").First());
if (!Validate2016ManagementComponents())
{
ProfilerTraceStatusTextBox.Text = "SQL 2016 Management Studio components required.\r\nComplete install from https://go.microsoft.com/fwlink/?LinkID=840946 and then open Profiler Trace Analysis again.";
btnImportProfilerTrace.Visible = false;
}
else
{
btnImportProfilerTrace.Visible = true;
string sqlForTraces = Properties.Settings.Default["SqlForProfilerTraceAnalysis"] as string;
if (m_analysisPath.EndsWith(".trc"))
{
mdfPath = m_analysisPath.Substring(0, m_analysisPath.LastIndexOf("\\") + 1) + "Analysis" + m_analysisPath.Substring(m_analysisPath.LastIndexOf("\\")).Replace(".trc", "").TrimEnd(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' }) + ".mdf";
}
else
{
mdfPath = m_analysisPath;
}
if (File.Exists(mdfPath) || File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf"))
{
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".ldf");
ProfilerTraceStatusTextBox.AppendText("Using trace data loaded into SQL .mdf at " + m_analysisPath + (m_analysisPath.EndsWith(".mdf") ? ".\r\n" : "\\Analysis\\.\r\n"));
new Thread(new ThreadStart(() => AttachProfilerTraceDB())).Start();
splitProfilerAnalysis.Visible = true;
btnImportProfilerTrace.Visible = false;
}
else
{
ProfilerTraceStatusTextBox.Text = "Trace file is not yet imported to database table for analysis. Import to perform analysis.";
}
}
}
}
}
void CompleteAnalysisTabsPopulationAfterZipExtraction()
{
string mdfPath = "";
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith("\\msmdsrv.ini")) || File.Exists(m_analysisPath + "\\msmdsrv.ini"))
{
tcAnalysis.TabPages.Add(new TabPage("Configuration")
{
ImageIndex = 0, Name = "Configuration"
});
tcAnalysis.TabPages["Configuration"].Controls.Add(GetStatusTextBox(File.ReadAllText(m_analysisPath + "\\msmdsrv.ini")));
}
bool dirhasdumps = false;
if (!File.Exists(m_analysisPath))
{
if (Directory.GetFiles(m_analysisPath, "*.mdmp", SearchOption.TopDirectoryOnly).Count() > 0)
{
dirhasdumps = true;
}
else
{
foreach (string dir in Directory.EnumerateDirectories(m_analysisPath))
{
if (!dir.Contains("\\$RECYCLE.BIN") &&
!dir.Contains("\\System Volume Information") &&
Directory.GetFiles(dir, "*.mdmp", SearchOption.AllDirectories).Count() > 0)
{
dirhasdumps = true;
break;
}
}
}
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".mdmp")) || dirhasdumps ||
(Directory.Exists(m_analysisPath + "\\Analysis") && Directory.GetFiles(m_analysisPath + "\\Analysis", "SSASDiag_MemoryDump_Analysis_*.mdf", SearchOption.TopDirectoryOnly).Count() > 0))
{
if (ValidateProfilerTraceDBConnectionStatus())
{
ucASDumpAnalyzer DumpAnalyzer = new ucASDumpAnalyzer(m_analysisPath, connSqlDb, StatusFloater);
DumpAnalyzer.Dock = DockStyle.Fill;
tcAnalysis.TabPages.Add(new TabPage("Memory Dumps")
{
ImageIndex = 1, Name = "Memory Dumps"
});
tcAnalysis.TabPages["Memory Dumps"].Controls.Add(DumpAnalyzer);
}
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".evtx")) ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + "_Application.evtx") ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + "_System.evtx"))
{
tcAnalysis.TabPages.Add(new TabPage("Event Logs")
{
ImageIndex = 2, Name = "Event Logs"
});
RichTextBox eventlogstatus = GetStatusTextBox("Right-click to open captured logs in Event Viewer. Check back soon for automated analysis of event logs.");
eventlogstatus.MouseUp += Eventlogstatus_MouseClick;
tcAnalysis.TabPages["Event Logs"].Controls.Add(eventlogstatus);
}
if ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".etl")) ||
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".cap")) ||
File.Exists(m_analysisPath + "\\" + AnalysisTraceID + ".etl") ||
File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.diag.log"))
{
tcAnalysis.TabPages.Add(new TabPage("Network Trace")
{
ImageIndex = 3, Name = "Network Trace"
});
RichTextBox txtNetworkAnalysis = GetStatusTextBox();
Button btnAnalyzeNetworkTrace = new Button()
{
Text = "Analyze Trace", Name = "btnAnalyzeNetworkTrace", Left = tcAnalysis.Width / 2 - 54, Width = 108, Top = 80, Visible = false
};
tcAnalysis.TabPages["Network Trace"].Controls.Add(btnAnalyzeNetworkTrace);
btnAnalyzeNetworkTrace.Click += btnAnalyzeNetworkTrace_Click;
tcAnalysis.TabPages["Network Trace"].Controls.Add(txtNetworkAnalysis);
if (File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log"))
{
txtNetworkAnalysis.Text = File.ReadAllText(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.log");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + "_NetworkAnalysis.diag.log");
}
else
{
btnAnalyzeNetworkTrace.Visible = true;
}
}
bool dirhasblgs = false;
if (!File.Exists(m_analysisPath))
{
if (Directory.GetFiles(m_analysisPath, "*.blg", SearchOption.TopDirectoryOnly).Count() > 0)
{
dirhasblgs = true;
}
else
{
foreach (string dir in Directory.EnumerateDirectories(m_analysisPath))
{
if (!dir.Contains("\\$RECYCLE.BIN") &&
!dir.Contains("\\System Volume Information") &&
Directory.GetFiles(dir, "*.blg", SearchOption.AllDirectories).Count() > 0)
{
dirhasblgs = true;
break;
}
}
}
}
if (Args.ContainsKey("perfmonanalyzer") && ((File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".blg")) || dirhasblgs ||
(Directory.Exists(m_analysisPath + "\\Analysis") && Directory.GetFiles(m_analysisPath + "\\Analysis", "SSASDiag_PerfMon_Analysis_*.mdf", SearchOption.TopDirectoryOnly).Count() > 0)))
{
if (ValidateProfilerTraceDBConnectionStatus())
{
tcAnalysis.TabPages.Add(new TabPage("Performance Logs")
{
ImageIndex = 4, Name = "Performance Logs"
});
ucASPerfMonAnalyzer PerfMonAnalyzer = new ucASPerfMonAnalyzer(m_analysisPath, connSqlDb, StatusFloater);
PerfMonAnalyzer.Dock = DockStyle.Fill;
tcAnalysis.TabPages["Performance Logs"].Controls.Add(PerfMonAnalyzer);
}
}
if (
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".trc")) ||
(File.Exists(m_analysisPath) && m_analysisPath.EndsWith(".mdf")) ||
(
!File.Exists(m_analysisPath) &&
(Directory.GetFiles(m_analysisPath, AnalysisTraceID + "*.trc").Count() > 0 ||
File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf"))
)
)
{
SetupSQLTextbox();
LogFeatureUse("Profiler Analysis", "Initializing analysis tab");
splitProfilerAnalysis.Visible = false;
ProfilerTraceStatusTextBox.Text = "";
tcAnalysis.TabPages.Add(HiddenTabPages.Where(t => t.Text == "Profiler Trace").First());
HiddenTabPages.Remove(HiddenTabPages.Where(t => t.Text == "Profiler Trace").First());
if (!Validate2017ManagementComponents())
{
ProfilerTraceStatusTextBox.Text = "SQL 2017 Management Studio components required.\r\nComplete install from https://go.microsoft.com/fwlink/?LinkID=840946 and then open Profiler Trace Analysis again.";
btnImportProfilerTrace.Visible = false;
}
else
{
btnImportProfilerTrace.Visible = true;
string sqlForTraces = Registry.CurrentUser.CreateSubKey(@"Software\SSASDiag").GetValue("SqlForProfilerTraceAnalysis", "") as string;
if (m_analysisPath.EndsWith(".trc"))
{
mdfPath = m_analysisPath.Substring(0, m_analysisPath.LastIndexOf("\\") + 1) + "Analysis" + m_analysisPath.Substring(m_analysisPath.LastIndexOf("\\")).Replace(".trc", "").TrimEnd(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' }) + ".mdf";
}
else
{
mdfPath = m_analysisPath;
}
if (File.Exists(mdfPath) || File.Exists(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf"))
{
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".mdf");
AddFileFromFolderIfAnlyzingZip(m_analysisPath + "\\Analysis\\" + AnalysisTraceID + ".ldf");
ProfilerTraceStatusTextBox.AppendText("Using trace data loaded into SQL .mdf at " + m_analysisPath + (m_analysisPath.EndsWith(".mdf") ? ".\r\n" : "\\Analysis\\.\r\n"));
new Thread(new ThreadStart(() => AttachProfilerTraceDB())).Start();
splitProfilerAnalysis.Visible = true;
btnImportProfilerTrace.Visible = false;
}
else
{
ProfilerTraceStatusTextBox.Text = "Trace file is not yet imported to database table for analysis. Import to perform analysis.";
}
}
}
// Prefer loading some tabs before others (Order of pref: Configuration (default if it exists anyway by alpha ordering as first tab), Network, Profiler, Memory Dump, then the others are all just placeholders so ignored.
if (!tcAnalysis.TabPages.ContainsKey("Configuration"))
{
if (tcAnalysis.TabPages.ContainsKey("tbProfilerTraces"))
{
tcAnalysis.SelectedTab = tcAnalysis.TabPages["tbProfilerTraces"];
}
else if (tcAnalysis.TabPages.ContainsKey("Network Trace"))
{
tcAnalysis.SelectedTab = tcAnalysis.TabPages["Network Trace"];
}
else if (tcAnalysis.TabPages.ContainsKey("Memory Dumps"))
{
tcAnalysis.SelectedTab = tcAnalysis.TabPages["Memory Dumps"];
}
}
tcAnalysis.Visible = true;
}
