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)); } } }