From f65bfbd6e84fdffc71353acfe7c9ccca5b117c45 Mon Sep 17 00:00:00 2001
From: xiazhengtongxue <133085197+xiazhengtongxue@users.noreply.github.com>
Date: 星期五, 23 一月 2026 17:28:41 +0800
Subject: [PATCH] 手动取消、异常上报和数据库维护脚本
---
项目代码/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs | 717 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 713 insertions(+), 4 deletions(-)
diff --git "a/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs" "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
index 5dc5329..c3e3d19 100644
--- "a/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
+++ "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
@@ -1,4 +1,130 @@
-锘縰sing NPOI.HSSF.Record;
+锘�//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澶╁墠涓擴RL涓虹壒瀹氬湴鍧�鐨勬棩蹇�)
+// 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[澧為暱閲廙B],
+// // 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;
@@ -15,11 +141,45 @@
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()
@@ -39,14 +199,15 @@
// 璁＄畻3涓湀鍓嶇殑鏃ユ湡
var threeMonthsAgo = DateTime.Now.AddMonths(-3);
// 璁＄畻3澶╁墠鐨勬棩鏈�
- var sevenDaysAgo = DateTime.Now.AddDays(-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涓湀鍓嶇殑鏃ュ織 鎴� (7澶╁墠涓擴RL涓虹壒瀹氬湴鍧�鐨勬棩蹇�)
+ // 鏋勫缓鍒犻櫎鏉′欢锛�3涓湀鍓嶇殑鏃ュ織 鎴� (3澶╁墠涓擴RL涓虹壒瀹氬湴鍧�鐨勬棩蹇�)
var result = BaseDal.Db.Deleteable<Sys_Log>()
.Where(log => log.EndDate < threeMonthsAgo ||
- (log.EndDate < sevenDaysAgo && log.Url == specificUrl))
+ (log.EndDate < threeDaysAgo && (log.Url == specificUrl || log.Url == anonUrl)))
.ExecuteCommand();
// 鏇存柊鏈�鍚庢墽琛屾棩鏈熶负浠婂ぉ
_lastExecutionDate = today;
@@ -54,9 +215,557 @@
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 [鏈�澶уぇ灏廙B],
+ growth/128.0 AS [澧為暱閲廙B],
+ 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;
+ }
+ }
}
}
\ No newline at end of file
--
Gitblit v1.9.3