| public static GetDbName ( string connectionString ) : string | ||
| connectionString | string | |
| return | string | |
private void CreateReportForDBCPUUsage()
{
List <Tuple <string, string> > usageDataPoints = new List <Tuple <string, string> >();
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
var currentDbName = Util.GetDbName(ConnectionString.ConnectionString);
using (var sqlConnection = new SqlConnection(masterConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
List <DateTime> lastNTimeEntries = dbExecutor.Query <DateTime>(string.Format("select distinct Top(5) time from sys.resource_usage where database_name = '{0}' order by time desc", currentDbName.ToString())).ToList();
foreach (DateTime time in lastNTimeEntries)
{
Console.WriteLine("Time ..................." + time.ToString());
var usageSeconds = dbExecutor.Query <Int32>(string.Format("select Sum(usage_in_seconds) from sys.resource_usage where time = '{0}' AND database_name = '{1}'", time.ToString(), currentDbName)).SingleOrDefault();
usageDataPoints.Add(new Tuple <string, string>(String.Format("{0:HH:mm}", time.ToLocalTime()), usageSeconds.ToString()));
}
}
usageDataPoints.Reverse(); //reverse it as the array returned will have latest hour as first entry.
JArray reportObject = ReportHelpers.GetJson(usageDataPoints);
ReportHelpers.CreateBlob(StorageAccount, "DBCPUTime" + string.Format("{0:MMdd}", DateTime.Now) + ".json", ContainerName, "application/json", ReportHelpers.ToStream(reportObject));
}
}
private void CreateReportForDataBaseEvents()
{
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
var currentDbName = Util.GetDbName(ConnectionString.ConnectionString);
using (var sqlConnection = new SqlConnection(masterConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var usageSeconds = dbExecutor.Query <DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
var json = new JavaScriptSerializer().Serialize(usageSeconds);
ReportHelpers.CreateBlob(StorageAccount, "DBDetailed" + LastNHours.ToString() + "Hour.json", ContainerName, "application/json", ReportHelpers.ToStream(json));
var throttlingEventCount = dbExecutor.Query <Int32>(string.Format("select count(*) from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName)).SingleOrDefault();
var additional_data = dbExecutor.Query <string>(string.Format("select additional_data from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize <AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName));
StringBuilder sb = new StringBuilder();
foreach (string data in additional_data)
{
if (data != null)
{
sb.Append(data + "\n");
}
}
if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventErrorThreshold && LastNHours == 1)
{
new SendAlertMailTask
{
AlertSubject = "Error: SQL Azure DB alert activated for throttling/deadlock event",
Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Error Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventErrorThreshold, throttlingEventCount, sb.ToString()),
AlertName = "Error: SQL Azure DB throttling/deadlock event",
Component = "SQL Azure Database",
Level = "Error"
}.ExecuteCommand();
}
else if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventWarningThreshold && LastNHours == 1)
{
new SendAlertMailTask
{
AlertSubject = "Warning: SQL Azure DB alert activated for throttling/deadlock event",
Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Warning Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventWarningThreshold, throttlingEventCount, sb.ToString()),
AlertName = "Warning: SQL Azure DB throttling/deadlock event",
Component = "SQL Azure Database",
Level = "Warning"
}.ExecuteCommand();
}
}
}
}
private DatabaseSize GetDataSize(string connectionString, int error, int warning)
{
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
int sizeInMb = dbExecutor.Query <int>(SqlQueryForDbSize).SingleOrDefault();
Int64 maxSizeInMb = dbExecutor.Query <Int64>(SqlQueryForMaxSize).SingleOrDefault();
double percentUsed = (sizeInMb / maxSizeInMb) * 100;
string edition = dbExecutor.Query <string>(SqlQueryForEdition).SingleOrDefault();
string dbName = Util.GetDbName(connectionString);
if (percentUsed > error)
{
new SendAlertMailTask
{
AlertSubject = string.Format("Error: SQL Azure database size alert activated for {0}", dbName),
Details = string.Format("DB Size excced the Error threshold percent.Current Used % {0}, Threshold % : {1}", percentUsed, error),
AlertName = "Error: SQL Azure DB alert for database size limit",
Component = string.Format("SQL Azure database-{0}", dbName),
Level = "Error"
}.ExecuteCommand();
}
else if (percentUsed > warning)
{
new SendAlertMailTask
{
AlertSubject = string.Format("Warning: SQL Azure database size alert activated for {0}", dbName),
Details = string.Format("DB Size excced the Warning threshold percent.Current Used % {0}, Threshold % : {1}", percentUsed, warning),
AlertName = "Warning: SQL Azure DB alert for database size limit",
Component = string.Format("SQL Azure database-{0}", dbName),
Level = "Warning"
}.ExecuteCommand();
}
return(new DatabaseSize(dbName, sizeInMb, maxSizeInMb, edition));
}
}
}