//using Microsoft.Data.SqlClient;
|
//using NPOI.HSSF.Record;
|
//using SkiaSharp;
|
//using System;
|
//using System.Collections.Generic;
|
//using System.Linq;
|
//using System.Linq.Expressions;
|
//using System.Text;
|
//using System.Threading.Tasks;
|
//using WIDESEAWCS_Core.BaseServices;
|
//using WIDESEAWCS_ISystemRepository;
|
//using WIDESEAWCS_ISystemServices;
|
//using WIDESEAWCS_Model.Models;
|
|
//namespace WIDESEAWCS_SystemServices
|
//{
|
// public class Sys_LogService : ServiceBase<Sys_Log, ISys_LogRepository>, ISys_LogService
|
// {
|
// private static DateTime? _lastExecutionDate = null;
|
// private readonly object _lock = new object();
|
// private const string DbName = "WIDESEA_HF";
|
// private const long MaxMdfSizeBytes = 8L * 1024 * 1024 * 1024; // 8GB
|
// private const int TargetShrinkSizeMB = 4500; // 4.5GB
|
|
// public Sys_LogService(ISys_LogRepository BaseDal) : base(BaseDal)
|
// {
|
|
// }
|
|
// public void DeleteOldLogs()
|
// {
|
// // 使用锁确保线程安全
|
// lock (_lock)
|
// {
|
// var today = DateTime.Today;
|
|
// // 检查今天是否已经执行过
|
// if (_lastExecutionDate.HasValue && _lastExecutionDate.Value.Date == today)
|
// {
|
// return;
|
// }
|
// try
|
// {
|
// // 计算3个月前的日期
|
// var threeMonthsAgo = DateTime.Now.AddMonths(-3);
|
// // 计算3天前的日期
|
// var sevenDaysAgo = DateTime.Now.AddDays(-3);
|
// // 特定URL
|
// var specificUrl = "http://11.2.30.141:10870/interfaces/api/amr/robotQuery";
|
|
// // 构建删除条件：3个月前的日志 或 (3天前且URL为特定地址的日志)
|
// var result = BaseDal.Db.Deleteable<Sys_Log>()
|
// .Where(log => log.EndDate < threeMonthsAgo ||
|
// (log.EndDate < sevenDaysAgo && log.Url == specificUrl))
|
// .ExecuteCommand();
|
// // 更新最后执行日期为今天
|
// _lastExecutionDate = today;
|
// }
|
// catch (Exception ex)
|
// {
|
// Console.WriteLine($"执行日志清理时发生错误：{ex.Message}");
|
// // 发生错误时不更新最后执行日期，以便重试
|
// }
|
// }
|
// }
|
// // 15天执行一次星期天2点
|
// public void CheckAndManageFileSize()
|
// {
|
// try
|
// {
|
|
// // --检查数据库文件空间使用情况
|
// //SELECT
|
// // name AS[文件逻辑名],
|
// // physical_name AS[物理路径],
|
// // size/ 128.0 AS[当前大小MB],
|
// // FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS[已用空间MB],
|
// // size / 128.0 - FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS[可用空间MB],
|
// // growth / 128 AS[增长量MB],
|
// // CASE is_percent_growth
|
// // WHEN 1 THEN CAST(growth AS VARCHAR) +'%'
|
// // ELSE CAST(growth/ 128 AS VARCHAR) +'MB'
|
// // END AS[增长方式]
|
// //FROM sys.database_files;
|
// using (var connection = new SqlConnection("Data Source=.;Initial Catalog=WIDESEA_HF;User ID=sa;Password=123456;Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
|
// {
|
// connection.Open();
|
|
// // 检查数据库文件大小
|
// var sizeCheckSql = @"SELECT name AS [文件逻辑名], size/128.0 AS [当前大小MB], FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已用空间MB] FROM sys.database_files WHERE name = @dbName";
|
|
// using (var command = new SqlCommand(sizeCheckSql, connection))
|
// {
|
// command.Parameters.AddWithValue("@dbName", DbName);
|
// using (var reader = command.ExecuteReader())
|
// {
|
// if (reader.Read())
|
// {
|
// double currentSizeMB = Convert.ToDouble(reader["当前大小MB"]);
|
// double usedSpaceMB = Convert.ToDouble(reader["已用空间MB"]);
|
|
// if (currentSizeMB > MaxMdfSizeBytes / (1024 * 1024))
|
// {
|
// // 执行收缩数据库
|
|
// }else
|
// {
|
// // 整理碎片>30%
|
|
// }
|
// }
|
// }
|
// }
|
// }
|
// }
|
// catch (Exception ex)
|
// {
|
// throw new Exception($"文件监控失败: {ex.Message}");
|
// }
|
// }
|
|
|
// }
|
//}
|
using Microsoft.Data.SqlClient;
|
using NPOI.HSSF.Record;
|
using SkiaSharp;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Text;
|
using System.Threading.Tasks;
|
using WIDESEAWCS_Core.BaseServices;
|
using WIDESEAWCS_ISystemRepository;
|
using WIDESEAWCS_ISystemServices;
|
using WIDESEAWCS_Model.Models;
|
|
namespace WIDESEAWCS_SystemServices
|
{
|
public class Sys_LogService : ServiceBase<Sys_Log, ISys_LogRepository>, ISys_LogService
|
{
|
private static DateTime? _lastExecutionDate = null;
|
private static DateTime? _lastFileSizeCheckDate = null;
|
private static DateTime? _lastBackupDate = null;
|
private readonly object _lock = new object();
|
private readonly object _fileSizeLock = new object();
|
private readonly object _backupLock = new object();
|
|
private const string DbName = "WIDESEA_HF";
|
private const string ConnectionString = "Data Source=11.2.30.112;Initial Catalog=WIDESEA_HF;User ID=kuka;Password=kuka;Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
|
private const long MaxMdfSizeBytes = 8L * 1024 * 1024 * 1024; // 8GB
|
private const int TargetShrinkSizeMB = 4500; // 4.5GB
|
private const double FragmentationThreshold = 30.0; // 30%碎片率阈值
|
private const int MaintenanceIntervalDays = 15; // 15天执行一次
|
private const int BackupRetentionDays = 30; // 备份保留30天
|
private const string BackupBasePath = @"D:\DatabaseBackups"; // 备份文件存储目录
|
private const bool UseBackupCompression = true; // 是否使用备份压缩
|
private const bool VerifyBackup = true; // 是否验证备份
|
private const int MaxBackupRetryCount = 3; // 最大重试次数
|
private const int BackupTimeoutSeconds = 7200; // 备份超时时间（秒）
|
|
public Sys_LogService(ISys_LogRepository BaseDal) : base(BaseDal)
|
{
|
// 确保备份目录存在
|
EnsureBackupDirectory();
|
}
|
|
private void EnsureBackupDirectory()
|
{
|
try
|
{
|
if (!Directory.Exists(BackupBasePath))
|
{
|
Directory.CreateDirectory(BackupBasePath);
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"创建备份目录失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
public void DeleteOldLogs()
|
{
|
// 使用锁确保线程安全
|
lock (_lock)
|
{
|
var today = DateTime.Today;
|
|
// 检查今天是否已经执行过
|
if (_lastExecutionDate.HasValue && _lastExecutionDate.Value.Date == today)
|
{
|
return;
|
}
|
try
|
{
|
// 计算3个月前的日期
|
var threeMonthsAgo = DateTime.Now.AddMonths(-3);
|
// 计算3天前的日期
|
var threeDaysAgo = DateTime.Now.AddDays(-3);
|
// 特定URL
|
var specificUrl = "http://11.2.30.141:10870/interfaces/api/amr/robotQuery";
|
var anonUrl = "http://11.2.30.111/anon/api/kuka/drivestatus";
|
|
// 构建删除条件：3个月前的日志 或 (3天前且URL为特定地址的日志)
|
var result = BaseDal.Db.Deleteable<Sys_Log>()
|
.Where(log => log.EndDate < threeMonthsAgo ||
|
(log.EndDate < threeDaysAgo && (log.Url == specificUrl || log.Url == anonUrl)))
|
.ExecuteCommand();
|
// 更新最后执行日期为今天
|
_lastExecutionDate = today;
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"执行日志清理时发生错误：{ex.Message}");
|
throw new Exception($"日志清理时发生错误: {ex.Message}", ex);
|
// 发生错误时不更新最后执行日期，以便重试
|
}
|
}
|
}
|
|
// 15天执行一次星期天2点
|
public void CheckAndManageFileSize()
|
{
|
// 检查是否符合执行条件：星期天、2点左右、距离上次执行至少15天
|
//if (!ShouldExecuteMaintenance())
|
//{
|
// return;
|
//}
|
|
try
|
{
|
using (var connection = new SqlConnection(ConnectionString))
|
{
|
connection.Open();
|
|
// 1. 备份数据库
|
BackupDatabase();
|
|
// 2. 检查数据库文件大小
|
CheckDatabaseFileSize(connection);
|
|
// 3. 检查并处理索引碎片
|
CheckAndHandleIndexFragmentation(connection);
|
|
// 4. 更新统计信息
|
UpdateStatistics(connection);
|
|
// 5. 清理旧备份
|
CleanupOldBackups();
|
|
// 更新最后检查日期
|
lock (_fileSizeLock)
|
{
|
_lastFileSizeCheckDate = DateTime.Now;
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"文件监控失败: {ex.Message}");
|
throw new Exception($"文件监控失败: {ex.Message}", ex);
|
}
|
}
|
|
public void BackupDatabase()
|
{
|
lock (_backupLock)
|
{
|
try
|
{
|
var today = DateTime.Today;
|
// 检查今天是否已经备份过
|
if (_lastBackupDate.HasValue && _lastBackupDate.Value.Date == today)
|
{
|
return;
|
}
|
var timestamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
|
var backupFileName = $"{DbName}_Full_{timestamp}.bak";
|
var backupFilePath = Path.Combine(BackupBasePath, backupFileName);
|
|
using (var connection = new SqlConnection(ConnectionString))
|
{
|
connection.Open();
|
// 获取数据库文件信息，计算备份文件大小
|
var dbSizeQuery = @"
|
SELECT
|
SUM(size/128.0) AS TotalSizeMB,
|
SUM(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS UsedSizeMB,
|
COUNT(*) AS FileCount
|
FROM sys.database_files
|
WHERE type IN (0, 1)"; // 数据文件和日志文件
|
|
double totalSizeMB = 0;
|
double usedSizeMB = 0;
|
int fileCount = 0;
|
|
using (var cmd = new SqlCommand(dbSizeQuery, connection))
|
using (var reader = cmd.ExecuteReader())
|
{
|
if (reader.Read())
|
{
|
totalSizeMB = Convert.ToDouble(reader["TotalSizeMB"]);
|
usedSizeMB = Convert.ToDouble(reader["UsedSizeMB"]);
|
fileCount = Convert.ToInt32(reader["FileCount"]);
|
}
|
}
|
|
|
// 检查磁盘空间是否足够
|
if (!CheckDiskSpace(backupFilePath, totalSizeMB))
|
{
|
throw new Exception("磁盘空间不足，无法执行备份");
|
}
|
|
// 构建备份SQL语句
|
var backupSql = BuildBackupSql(backupFilePath);
|
|
// 执行备份（带重试机制）
|
ExecuteBackupWithRetry(connection, backupSql, backupFilePath);
|
|
// 验证备份文件
|
if (VerifyBackup)
|
{
|
VerifyBackupFile(connection, backupFilePath);
|
}
|
|
// 更新最后备份日期
|
_lastBackupDate = DateTime.Now;
|
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"数据库备份失败: {ex.Message}");
|
throw new Exception($"数据库备份失败: {ex.Message}", ex);
|
}
|
}
|
}
|
|
private bool CheckDiskSpace(string backupFilePath, double estimatedBackupSizeMB)
|
{
|
try
|
{
|
var driveInfo = new DriveInfo(Path.GetPathRoot(backupFilePath));
|
var freeSpaceMB = driveInfo.AvailableFreeSpace / (1024 * 1024);
|
|
// 需要至少1.5倍的空间来确保备份顺利进行
|
var requiredSpaceMB = estimatedBackupSizeMB * 1.5;
|
|
if (freeSpaceMB < requiredSpaceMB)
|
{
|
return false;
|
}
|
|
return true;
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"检查磁盘空间时出错: {ex.Message}");
|
return false;
|
}
|
}
|
|
private string BuildBackupSql(string backupFilePath)
|
{
|
var compressionOption = UseBackupCompression ? "COMPRESSION" : "NO_COMPRESSION";
|
var verifyOption = VerifyBackup ? ", CHECKSUM" : "";
|
|
return $@"
|
BACKUP DATABASE [{DbName}]
|
TO DISK = '{backupFilePath.Replace("'", "''")}'
|
WITH {compressionOption}
|
{verifyOption}
|
, STATS = 5
|
, DESCRIPTION = 'Full database backup created by automated maintenance on {DateTime.Now:yyyy-MM-dd HH:mm:ss}'
|
, MAXTRANSFERSIZE = 4194304
|
, BLOCKSIZE = 65536
|
, BUFFERCOUNT = 10";
|
}
|
|
private void ExecuteBackupWithRetry(SqlConnection connection, string backupSql, string backupFilePath)
|
{
|
int retryCount = 0;
|
while (retryCount < MaxBackupRetryCount)
|
{
|
try
|
{
|
|
using (var cmd = new SqlCommand(backupSql, connection))
|
{
|
cmd.CommandTimeout = BackupTimeoutSeconds;
|
|
var rowsAffected = cmd.ExecuteNonQuery();
|
return; // 成功则退出
|
}
|
}
|
catch (Exception ex)
|
{
|
retryCount++;
|
|
if (retryCount >= MaxBackupRetryCount)
|
{
|
// 删除可能损坏的备份文件
|
try
|
{
|
if (File.Exists(backupFilePath))
|
{
|
File.Delete(backupFilePath);
|
}
|
}
|
catch { }
|
|
throw new Exception($"备份失败，已达到最大重试次数({MaxBackupRetryCount}次): {ex.Message}", ex);
|
}
|
|
Console.WriteLine($"备份失败，{MaxBackupRetryCount - retryCount}次重试机会: {ex.Message}");
|
Thread.Sleep(5000); // 等待5秒后重试
|
}
|
}
|
}
|
|
private void VerifyBackupFile(SqlConnection connection, string backupFilePath)
|
{
|
try
|
{
|
|
var verifySql = $@"
|
RESTORE VERIFYONLY
|
FROM DISK = '{backupFilePath.Replace("'", "''")}'
|
WITH CHECKSUM";
|
|
using (var cmd = new SqlCommand(verifySql, connection))
|
{
|
cmd.CommandTimeout = 300; // 5分钟超时
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
// 验证失败时删除备份文件
|
try
|
{
|
if (File.Exists(backupFilePath))
|
{
|
File.Delete(backupFilePath);
|
}
|
}
|
catch { }
|
|
throw new Exception($"备份文件验证失败: {ex.Message}", ex);
|
}
|
}
|
|
private void CleanupOldBackups()
|
{
|
try
|
{
|
|
if (!Directory.Exists(BackupBasePath))
|
{
|
Console.WriteLine("备份目录不存在，跳过清理");
|
return;
|
}
|
|
var cutoffDate = DateTime.Now.AddDays(-BackupRetentionDays);
|
var backupFiles = Directory.GetFiles(BackupBasePath, $"{DbName}_Full_*.bak");
|
int deletedCount = 0;
|
|
foreach (var file in backupFiles)
|
{
|
try
|
{
|
var fileInfo = new FileInfo(file);
|
if (fileInfo.CreationTime < cutoffDate)
|
{
|
fileInfo.Delete();
|
deletedCount++;
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"删除备份文件 {file} 时出错: {ex.Message}");
|
// 继续处理其他文件
|
}
|
}
|
|
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"清理旧备份时出错: {ex.Message}");
|
// 不抛出异常，避免影响主流程
|
}
|
}
|
|
private bool ShouldExecuteMaintenance()
|
{
|
lock (_fileSizeLock)
|
{
|
var now = DateTime.Now;
|
|
// 检查今天是否是星期天
|
if (now.DayOfWeek != DayOfWeek.Sunday)
|
{
|
return false;
|
}
|
|
// 检查时间是否是1点左右（允许1:00-1:59）
|
if (now.Hour != 1)
|
{
|
return false;
|
}
|
|
// 检查是否已经执行过
|
if (_lastFileSizeCheckDate.HasValue)
|
{
|
// 检查距离上次执行是否满足15天间隔
|
var daysSinceLastCheck = (now - _lastFileSizeCheckDate.Value).TotalDays;
|
if (daysSinceLastCheck < MaintenanceIntervalDays)
|
{
|
return false;
|
}
|
}
|
|
return true;
|
}
|
}
|
|
private void CheckDatabaseFileSize(SqlConnection connection)
|
{
|
try
|
{
|
var sizeCheckSql = @"
|
SELECT
|
name AS [文件逻辑名],
|
physical_name AS [物理路径],
|
size/128.0 AS [当前大小MB],
|
FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已用空间MB],
|
size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [可用空间MB],
|
max_size/128.0 AS [最大大小MB],
|
growth/128.0 AS [增长量MB],
|
CASE is_percent_growth
|
WHEN 1 THEN '百分比增长'
|
ELSE '固定大小增长'
|
END AS [增长方式]
|
FROM sys.database_files
|
WHERE type = 0"; // 0表示数据文件，1表示日志文件
|
|
using (var command = new SqlCommand(sizeCheckSql, connection))
|
{
|
using (var reader = command.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
string logicalName = reader["文件逻辑名"].ToString();
|
double currentSizeMB = Convert.ToDouble(reader["当前大小MB"]);
|
double usedSpaceMB = Convert.ToDouble(reader["已用空间MB"]);
|
double freeSpaceMB = Convert.ToDouble(reader["可用空间MB"]);
|
|
|
// 检查是否需要收缩
|
if (currentSizeMB * 1024 * 1024 > MaxMdfSizeBytes)
|
{
|
Console.WriteLine("数据库文件超过8GB，开始收缩...");
|
ShrinkDatabaseFile(connection, logicalName);
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"检查数据库文件大小时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void ShrinkDatabaseFile(SqlConnection connection, string logicalName)
|
{
|
try
|
{
|
|
// 收缩数据库文件
|
string shrinkSql = $"DBCC SHRINKFILE (N'{logicalName}', {TargetShrinkSizeMB})";
|
using (var command = new SqlCommand(shrinkSql, connection))
|
{
|
int rowsAffected = command.ExecuteNonQuery();
|
Console.WriteLine($"数据库文件收缩完成，目标大小: {TargetShrinkSizeMB} MB");
|
}
|
|
// 重新组织索引以避免收缩导致的碎片
|
ReorganizeAllIndexes(connection);
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"收缩数据库文件时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void CheckAndHandleIndexFragmentation(SqlConnection connection)
|
{
|
try
|
{
|
|
// 查询所有表的索引碎片率
|
string fragmentationSql = @"
|
SELECT
|
OBJECT_NAME(ips.object_id) AS TableName,
|
i.name AS IndexName,
|
ips.index_type_desc AS IndexType,
|
ips.avg_fragmentation_in_percent AS FragmentationPercent,
|
ips.page_count AS PageCount
|
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
|
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
|
WHERE ips.avg_fragmentation_in_percent > 0
|
AND ips.index_id > 0 -- 排除堆
|
AND ips.page_count > 100 -- 只处理超过100页的索引
|
ORDER BY ips.avg_fragmentation_in_percent DESC";
|
|
using (var command = new SqlCommand(fragmentationSql, connection))
|
using (var reader = command.ExecuteReader())
|
{
|
var indexesToReorganize = new List<(string TableName, string IndexName, double Fragmentation)>();
|
|
while (reader.Read())
|
{
|
string tableName = reader["TableName"].ToString();
|
string indexName = reader["IndexName"].ToString();
|
double fragmentation = Convert.ToDouble(reader["FragmentationPercent"]);
|
long pageCount = Convert.ToInt64(reader["PageCount"]);
|
|
Console.WriteLine($"表: {tableName}, 索引: {indexName}, 碎片率: {fragmentation:F2}%, 页数: {pageCount}");
|
|
if (fragmentation > FragmentationThreshold)
|
{
|
indexesToReorganize.Add((tableName, indexName, fragmentation));
|
}
|
}
|
|
reader.Close(); // 关闭reader以执行后续命令
|
|
// 处理需要重新组织的索引
|
if (indexesToReorganize.Any())
|
{
|
|
foreach (var index in indexesToReorganize)
|
{
|
ReorganizeIndex(connection, index.TableName, index.IndexName);
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"检查索引碎片时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void ReorganizeIndex(SqlConnection connection, string tableName, string indexName)
|
{
|
try
|
{
|
string reorganizeSql = $"ALTER INDEX [{indexName}] ON [{tableName}] REORGANIZE";
|
using (var command = new SqlCommand(reorganizeSql, connection))
|
{
|
command.CommandTimeout = 600; // 设置10分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"重新组织索引 {tableName}.{indexName} 时出错: {ex.Message}");
|
// 继续处理其他索引
|
}
|
}
|
|
private void ReorganizeAllIndexes(SqlConnection connection)
|
{
|
try
|
{
|
string reorganizeAllSql = @"
|
DECLARE @TableName NVARCHAR(128)
|
DECLARE @IndexName NVARCHAR(128)
|
DECLARE @Sql NVARCHAR(MAX)
|
|
DECLARE IndexCursor CURSOR FOR
|
SELECT
|
OBJECT_NAME(i.object_id) AS TableName,
|
i.name AS IndexName
|
FROM sys.indexes i
|
WHERE i.index_id > 0 -- 排除堆
|
AND OBJECT_NAME(i.object_id) IS NOT NULL
|
|
OPEN IndexCursor
|
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
|
|
WHILE @@FETCH_STATUS = 0
|
BEGIN
|
SET @Sql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE'
|
BEGIN TRY
|
EXEC sp_executesql @Sql
|
PRINT '已重新组织索引: ' + @TableName + '.' + @IndexName
|
END TRY
|
BEGIN CATCH
|
PRINT '重新组织索引 ' + @TableName + '.' + @IndexName + ' 时出错: ' + ERROR_MESSAGE()
|
END CATCH
|
|
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
|
END
|
|
CLOSE IndexCursor
|
DEALLOCATE IndexCursor";
|
|
using (var command = new SqlCommand(reorganizeAllSql, connection))
|
{
|
command.CommandTimeout = 1800; // 设置30分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"重新组织所有索引时出错: {ex.Message}");
|
throw;
|
}
|
}
|
private void UpdateStatistics(SqlConnection connection)
|
{
|
try
|
{
|
// 更新所有表的统计信息
|
string updateStatsSql = "EXEC sp_updatestats";
|
using (var command = new SqlCommand(updateStatsSql, connection))
|
{
|
command.CommandTimeout = 600; // 设置10分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"更新统计信息时出错: {ex.Message}");
|
throw;
|
}
|
}
|
// 提供一个方法供任务调度器调用
|
public void ScheduleDatabaseMaintenance()
|
{
|
try
|
{
|
// 1. 删除旧日志
|
DeleteOldLogs();
|
// 检查是否符合执行条件
|
if (!ShouldExecuteMaintenance())
|
{
|
return;
|
}
|
// 2. 检查和管理文件大小
|
CheckAndManageFileSize();
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"执行数据库维护任务时出错: {ex.Message}");
|
throw;
|
}
|
}
|
}
|
}
|
