/// <summary>
/// Process handler main task.
/// </summary>
/// <param name="timer"></param>
protected override void OnExecute(Timer timer)
{
using (var command = DaoFactory.CreateCommand())
{
var database = GetString("Database");
var backupDirectory = GetString("BackupDirectory");
var fileName = String.Format("{0}_{1:0000}{2:00}{3:00}.bak", database, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
var backup = String.Concat(backupDirectory, fileName);
var directory = new DirectoryInfo(backupDirectory);
if (!directory.Exists)
{
directory.Create();
}
STrace.Trace(GetType().FullName, String.Format("Performing database backup: {0} - {1}", database, backup));
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
command.CommandText = GetBackupCommand(database, backup);
command.ExecuteNonQuery();
foreach (var file in directory.GetFiles().Where(file => !file.Name.Equals(fileName)))
{
file.Delete();
}
}
}
/// <summary>
/// Gets a list of stadistics to be updated.
/// </summary>
/// <returns></returns>
private IEnumerable <Stadistic> GetIndexesToUpdate()
{
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = GetUpdateIndexesScript();
command.CommandType = CommandType.Text;
return(MapResults(command));
}
}
/// <summary>
/// Get current missing idexes recommendations.
/// </summary>
/// <returns></returns>
private List <MissingIndex> GetRecommendations()
{
using (var command = DaoFactory.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = GetScript();
command.CommandTimeout = 0;
return(MapResults(command.ExecuteReader()));
}
}
/// <summary>
/// Gets information about the expensive queries associated to the specified sql script.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private ICollection <ExpensiveQuery> GetQueries(String sql)
{
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
return(MapResults(command.ExecuteReader()));
}
}
/// <summary>
/// Gets the list of all indeces that are not being used.
/// </summary>
/// <returns></returns>
private IEnumerable <Index> GetNonUsedIndexes()
{
var script = GetIndexesCommand();
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = script;
command.CommandType = CommandType.Text;
return(MapResults(command));
}
}
/// <summary>
/// Gets the list of all indeces that need to be defragmented.
/// </summary>
/// <returns></returns>
private IEnumerable <Index> GetIndexesToDefragmentate()
{
var script = GetIndexesCommand();
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = script;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
return(MapResults(command));
}
}
/// <summary>
/// Process handler main task.
/// </summary>
/// <param name="timer"></param>
protected override void OnExecute(Timer timer)
{
using (var command = DaoFactory.CreateCommand())
{
var database = GetString("Database");
var logFile = GetString("LogFile");
STrace.Trace(GetType().FullName, String.Format("Shrinking database log file: {0} - {1}", database, logFile));
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
command.CommandText = String.Format("USE [{0}] DBCC SHRINKFILE (N'{1}' , 0, TRUNCATEONLY)", database, logFile);
command.ExecuteNonQuery();
}
}
/// <summary>
/// Updates the specified stadistic.
/// </summary>
/// <param name="stadistic"></param>
private void Update(Stadistic stadistic)
{
STrace.Trace(GetType().FullName, String.Format("Updating stadistics for index: {0} ({1})", stadistic.Index, stadistic.Table));
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = stadistic.Command;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
STrace.Trace(GetType().FullName, String.Format("{0} stadistics to update.", --_stadistics));
DoSleep();
}
}
/// <summary>
/// Defragmentates the specified index.
/// </summary>
/// <param name="index"></param>
private void Defragmentate(Index index)
{
STrace.Trace(GetType().FullName, String.Format("Performing defragmentation of index: {0} ({1})", index.IndexName, index.TableName));
using (var command = DaoFactory.CreateCommand())
{
command.CommandText = index.Command;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
STrace.Trace(GetType().FullName, String.Format("{0} indexes to defragment.", --_indexes));
DoSleep();
}
}