/// <summary>
/// Get a job tasl result as a formatted string
/// </summary>
/// <param name="result"></param>
/// <returns></returns>
private string GetFormattedResult(DatabaseMaintenanceTaskResult result)
{
if (result.HasException)
{
return($"<i class='fa fa-circle text-danger'></i> { result.Title}");
}
else
{
var icon = "<i class='fa fa-circle text-success'></i>";
return($"{icon} {result.Title} ({result.Elapsed.TotalMilliseconds:N0}ms)");
}
}
/// <summary>
/// Checks the integrity of the database
/// </summary>
/// <param name="commandTimeout">The command timeout.</param>
/// <param name="alertEmail">The alert email.</param>
/// <returns></returns>
private bool IntegrityCheck(int commandTimeout, string alertEmail)
{
string databaseName = new RockContext().Database.Connection.Database;
string integrityQuery = $"DBCC CHECKDB('{ databaseName }',NOINDEX) WITH PHYSICAL_ONLY, NO_INFOMSGS";
bool checkPassed = true;
Stopwatch stopwatch = Stopwatch.StartNew();
// DBCC CHECKDB will return a count of how many issues there were
int integrityErrorCount = DbService.ExecuteCommand(integrityQuery, System.Data.CommandType.Text, null, commandTimeout);
stopwatch.Stop();
var databaseMaintenanceTaskResult = new DatabaseMaintenanceTaskResult
{
Title = "Integrity Check",
Elapsed = stopwatch.Elapsed
};
_databaseMaintenanceTaskResults.Add(databaseMaintenanceTaskResult);
if (integrityErrorCount > 0)
{
// oh no...
checkPassed = false;
string errorMessage = $"Some errors were reported when running a database integrity check on your Rock database. We'd recommend running the command below under 'Admin Tools > Power Tools > SQL Command' to get further details. <p>DBCC CHECKDB ('{ databaseName }') WITH NO_INFOMSGS, ALL_ERRORMSGS</p>";
var mergeFields = Lava.LavaHelper.GetCommonMergeFields(null, null);
mergeFields.Add("ErrorMessage", errorMessage);
mergeFields.Add("Errors", integrityErrorCount);
databaseMaintenanceTaskResult.Exception = new Exception(errorMessage);
if (alertEmail.IsNotNullOrWhiteSpace())
{
var globalAttributes = GlobalAttributesCache.Get();
string emailHeader = globalAttributes.GetValue("EmailHeader");
string emailFooter = globalAttributes.GetValue("EmailFooter");
string messageBody = $"{emailHeader} {errorMessage} <p><small>This message was generated from the Rock Database Maintenance Job</small></p>{emailFooter}";
var emailMessage = new RockEmailMessage();
var alertEmailList = alertEmail.Split(',').ToList();
var recipients = alertEmailList.Select(a => RockEmailMessageRecipient.CreateAnonymous(a, mergeFields)).ToList();
emailMessage.Subject = "Rock: Database Integrity Check Error";
emailMessage.Message = messageBody;
emailMessage.Send();
}
}
return(checkPassed);
}
/// <summary>
/// Rebuilds the fragmented indexes.
/// </summary>
/// <param name="jobContext">The job context.</param>
/// <param name="commandTimeoutSeconds">The command timeout seconds.</param>
private void RebuildFragmentedIndexes(IJobExecutionContext jobContext, int commandTimeoutSeconds)
{
JobDataMap dataMap = jobContext.JobDetail.JobDataMap;
int minimumIndexPageCount = dataMap.GetString("MinimumIndexPageCount").AsInteger();
int minimumFragmentationPercentage = dataMap.GetString("MinimumFragmentationPercentage").AsInteger();
int rebuildThresholdPercentage = dataMap.GetString("RebuildThresholdPercentage").AsInteger();
bool useONLINEIndexRebuild = dataMap.GetString("UseONLINEIndexRebuild").AsBoolean();
if (useONLINEIndexRebuild &&
!(RockInstanceConfig.Database.Platform == RockInstanceDatabaseConfiguration.PlatformSpecifier.AzureSql ||
RockInstanceConfig.Database.Edition.Contains("Enterprise")))
{
// Online index rebuild is only available for Azure SQL or SQL Enterprise.
RockLogger.Log.Information(RockLogDomains.Jobs, "Database Maintenance - Online Index Rebuild option is selected but not available for the current database platform.");
useONLINEIndexRebuild = false;
}
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@PageCountLimit", minimumIndexPageCount);
parms.Add("@MinFragmentation", minimumFragmentationPercentage);
parms.Add("@MinFragmentationRebuild", rebuildThresholdPercentage);
parms.Add("@UseONLINEIndexRebuild", useONLINEIndexRebuild);
var qry = @"
SELECT
dbschemas.[name] as [Schema],
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
dbindexes.[type_desc] as [IndexType],
CONVERT(INT, indexstats.avg_fragmentation_in_percent) as [FragmentationPercent] ,
indexstats.page_count as [PageCount]
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
AND indexstats.page_count > @PageCountLimit
AND indexstats.avg_fragmentation_in_percent > @MinFragmentation
";
var dataTable = DbService.GetDataTable(qry, System.Data.CommandType.Text, parms, commandTimeoutSeconds);
var rebuildFillFactorOption = "FILLFACTOR = 80";
var indexInfoList = dataTable.Rows.OfType <DataRow>().Select(row => new
{
SchemaName = row["Schema"].ToString(),
TableName = row["Table"].ToString(),
IndexName = row["Index"].ToString(),
IndexType = row["IndexType"].ToString(),
FragmentationPercent = row["FragmentationPercent"].ToString().AsIntegerOrNull()
});
// let C# do the sorting.
var sortedIndexInfoList = indexInfoList.OrderBy(a => a.TableName).ThenBy(a => a.IndexName);
foreach (var indexInfo in sortedIndexInfoList)
{
jobContext.UpdateLastStatusMessage($"Rebuilding Index [{indexInfo.TableName}].[{indexInfo.IndexName}]");
Stopwatch stopwatch = Stopwatch.StartNew();
DatabaseMaintenanceTaskResult databaseMaintenanceTaskResult = new DatabaseMaintenanceTaskResult
{
Title = $"Rebuild [{indexInfo.TableName}].[{indexInfo.IndexName}]"
};
_databaseMaintenanceTaskResults.Add(databaseMaintenanceTaskResult);
var rebuildSQL = $"ALTER INDEX [{indexInfo.IndexName}] ON [{indexInfo.SchemaName}].[{indexInfo.TableName}]";
if (indexInfo.FragmentationPercent > minimumFragmentationPercentage)
{
var commandOption = rebuildFillFactorOption;
if (useONLINEIndexRebuild && (indexInfo.IndexType != "SPATIAL" && indexInfo.IndexType != "XML"))
{
commandOption = commandOption + $", ONLINE = ON";
}
rebuildSQL += $" REBUILD WITH ({commandOption})";
}
else
{
rebuildSQL += $" REORGANIZE";
}
try
{
DbService.ExecuteCommand(rebuildSQL, System.Data.CommandType.Text, null, commandTimeoutSeconds);
stopwatch.Stop();
databaseMaintenanceTaskResult.Elapsed = stopwatch.Elapsed;
}
catch (Exception ex)
{
ExceptionLogService.LogException(new Exception($"Error rebuilding index [{indexInfo.TableName}].[{indexInfo.IndexName}]", ex));
databaseMaintenanceTaskResult.Exception = ex;
}
}
}