//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, 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() // .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; using WIDESEAWCS_Core.BaseServices; using WIDESEAWCS_ISystemRepository; using WIDESEAWCS_ISystemServices; using WIDESEAWCS_Model.Models; namespace WIDESEAWCS_SystemServices { public class Sys_LogService : ServiceBase, 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 = 7L * 1024 * 1024 * 1024; // 7GB 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 static readonly string BackupBasePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "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 { var result = BaseDal.Db.Deleteable().ExecuteCommand(); //// 计算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() // .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"]); } } // 构建备份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 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 && now.Hour != 2) { 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; } } /// /// 递归获取文件信息 /// /// /// private DirInfo GetDirInfo(string dirPath) { //当前文件夹 var dirInfo = new DirInfo(); dirInfo.dirName = Path.GetFileName(dirPath); //子文件 List files = new List(); foreach (var file in Directory.GetFiles(dirPath)) { files.Add(new FileDataInfo() { filePath = file, fileName = Path.GetFileName(file) }); } //子文件夹 var dirs = Directory.GetDirectories(dirPath); dirInfo.dirs = new List(); foreach (var dir in dirs) { dirInfo.dirs.Add(GetDirInfo(dir)); } //子文件夹，与子目录合并 foreach (var file in files) { dirInfo.dirs.Add(new DirInfo() { dirPath = file.filePath, dirName = file.fileName }); } return dirInfo; } /// /// 获取日志文件列表 /// /// public WebResponseContent GetLogList() { WebResponseContent content = new WebResponseContent(); string path = Path.Combine(AppContext.BaseDirectory, "log"); if (!Directory.Exists(path)) { return content.Error("暂无日志文件"); } content.Data = GetDirInfo(path); content.OK(); return content; } /// /// 获取文件内容 /// /// /// public WebResponseContent GetLogData(GetLogParm parm) { WebResponseContent content = new WebResponseContent(); string res = ""; //是否读取到最后 bool isEnd = false; long startIndex = 0; //文件大小 long len = 0; try { if (!File.Exists(parm.path)) { throw new Exception($"文件{parm.path}不存在！"); } using (FileStream fs = new FileStream(parm.path, FileMode.Open, FileAccess.Read, FileShare.Read)) { //最大读取大小 int maxsize = (int)(1024 * parm.maxsize_KB); len = fs.Length; long startPos = (long)(len * (parm.percent / 100));//起始位置 long readLen = len - startPos;//读取长度 //向前加载 if (parm.topStartPos != 0) { startPos = parm.topStartPos - maxsize; if (startPos < 0) { //已读到起始位 startPos = 0; readLen = parm.topStartPos; } else { readLen = maxsize; } } else { //读取大小是否超出最大长度 if (readLen > maxsize) { readLen = maxsize; } else { isEnd = true; } } //加载100%，按最大内容读取 if (parm.percent == 100) { if (len < maxsize) { startPos = 0; readLen = len; } else { startPos = len - maxsize; readLen = maxsize; } } fs.Seek(startPos, SeekOrigin.Begin); var buffer = new byte[readLen]; fs.Read(buffer, 0, (int)readLen); startIndex = startPos; if (startPos != 0 && (parm.percent != 0 || parm.topStartPos != 0)) { //不是从头加载，删除可能不完整的第一行 int skipCount = 0; for (int i = 0; i < buffer.Length; i++) { if (buffer[i] == 10) { skipCount = i; break; } } if (skipCount != 0) { //去掉换行 skipCount++; //下次读取前延 startIndex += skipCount; } res = Encoding.UTF8.GetString(buffer.Skip(skipCount).ToArray()); } else { res = Encoding.UTF8.GetString(buffer); } } } catch (Exception ex) { return content.Error(ex.Message); } return content.OK(data: new { content = res, isEnd, startIndex, len }); } } }