333 lines
15 KiB
C#
333 lines
15 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Windows.Forms;
|
|
|
|
namespace AdvertsingProfitControl
|
|
{
|
|
public partial class FrmIntegrityCheck : Form
|
|
{
|
|
//Dictionary<objectId, object>
|
|
private readonly Dictionary<int, DatabaseTable> _databaseObjectsDictionary = new Dictionary<int, DatabaseTable>();
|
|
private readonly Dictionary<int, DatabaseTable.RepairLevel> _errorsDictionary = new Dictionary<int, DatabaseTable.RepairLevel>();
|
|
private readonly List<DatabaseDbCcResult> _databaseDbCcResults = new List<DatabaseDbCcResult>();
|
|
|
|
public FrmIntegrityCheck(string connectionString)
|
|
{
|
|
InitializeComponent();
|
|
RunDbCheck(connectionString);
|
|
}
|
|
|
|
private void RunDbCheck(string connectionString)
|
|
{
|
|
var reportsTable = new DataTable();
|
|
try
|
|
{
|
|
using (var connection = new SqlConnection(connectionString))
|
|
{
|
|
var query = @"IF NOT OBJECT_ID('tempdb..#CheckDB') IS NULL
|
|
DROP TABLE #CheckDB;
|
|
-- Creating temporary table for CheckDB result.
|
|
CREATE TABLE #CheckDB
|
|
([Error] int ,[Level] int ,[State] int
|
|
,[MessageText] varchar(7000)
|
|
,[RepairLevel] varchar(512)
|
|
,[Status] int ,[DbId] int ,[DbFragId] bigint
|
|
,[ObjectID] bigint ,[IndexId] bigint
|
|
,[PartitionId] bigint ,[AllocUnitId] bigint
|
|
,[RidDbId] int ,[RidPruId] int ,[File] int
|
|
,[Page] int ,[Slot] int ,[RefDbID] int
|
|
,[RefPruId] int ,[RefFile] int ,[RefPage] int
|
|
,[RefSlot] int ,[Allocation] int);
|
|
-- Execute CheckDB and insert result to temp table
|
|
INSERT INTO #CheckDB
|
|
([Error], [Level], [State], [MessageText], [RepairLevel],
|
|
[Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId],
|
|
[AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID],
|
|
[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation])
|
|
EXEC ('DBCC CHECKDB(''AdvertisingProfitControl'') WITH TABLERESULTS');
|
|
-- Show final summary message first with total count of errors
|
|
-- and warnings. Error number 8989 is for the summary messages,
|
|
-- the messages that begin with 'CHECKDB'.
|
|
-- Query the relevant data from the DBCC result set.
|
|
SELECT OBJ.name AS Name, OBJ.type_desc AS Type,
|
|
IDX.Name AS PrimaryKey,
|
|
CDB.RepairLevel, MessageText, CDB.Error, CDB.ObjectID
|
|
FROM #CheckDB AS CDB
|
|
LEFT JOIN sys.objects AS OBJ
|
|
ON CDB.ObjectId = OBJ.object_id
|
|
|
|
LEFT JOIN sys.indexes AS IDX
|
|
ON CDB.ObjectId = IDX.object_id
|
|
|
|
AND CDB.IndexID = IDX.index_id
|
|
|
|
LEFT JOIN sys.allocation_units AS ALU
|
|
ON CDB.AllocUnitId = ALU.allocation_unit_id
|
|
ORDER BY Name;
|
|
--Post cleanup.
|
|
DROP TABLE #CheckDB;";
|
|
using (var command = new SqlCommand(query, connection))
|
|
{
|
|
connection.Open();
|
|
reportsTable.Load(command.ExecuteReader());
|
|
}
|
|
}
|
|
/*OBJ.name AS Name, OBJ.type_desc AS Type, IDX.Name AS PrimaryKey,
|
|
CDB.RepairLevel, MessageText, CDB.Error, CDB.ObjectID */
|
|
//<object ID, repair level>
|
|
_errorsDictionary.Clear();
|
|
foreach (DataRow row in reportsTable.Rows)
|
|
{
|
|
var objectId = int.Parse(row[6].ToString());
|
|
var objectTypeId = int.Parse(row[5].ToString()); //The Error column
|
|
var repairLevel = row[3].ToString();
|
|
//Check if the object is a service message.
|
|
if (objectTypeId == (int) DatabaseObjectType.ServiceBrokerMessage)
|
|
{
|
|
//newObject.Type = DatabaseObjectType.ServiceBrokerMessage;
|
|
//Do nothing, we don't care about service broker messages.
|
|
}
|
|
//Or a DBCC summary statement.
|
|
else if (objectTypeId == (int) DatabaseObjectType.DbCcResult)
|
|
{
|
|
var dbCcResult = ParseDbCcResultMessage(row[4].ToString());
|
|
_databaseDbCcResults.Add(dbCcResult);
|
|
}
|
|
//Check to see if the object is a database table
|
|
else if (objectTypeId == (int) DatabaseObjectType.Table)
|
|
{
|
|
var table = ParseDatabaseTableMessage(row[4].ToString());
|
|
table.Id = objectId;
|
|
table.Error = objectTypeId;
|
|
if (_errorsDictionary.ContainsKey(objectId))
|
|
{
|
|
table.RecommenedRepairLevel = _errorsDictionary[objectId];
|
|
//_errorsDictionary.Remove(objectId);
|
|
}
|
|
_databaseObjectsDictionary.Add(table.Id, table);
|
|
}
|
|
//Check to see if there is a repair level present, this should only occur in error messages
|
|
//outside of the DBCC summary messages.
|
|
if (repairLevel != string.Empty)
|
|
{
|
|
var recommenedRepairLevel = DatabaseTable.RepairLevel.None;
|
|
//Use the above ID number to as an index in the dictionary.
|
|
//repair_allow_data_loss
|
|
//repair_rebuild
|
|
if (repairLevel == "repair_allow_data_loss")
|
|
{
|
|
recommenedRepairLevel = DatabaseTable.RepairLevel.AllowDataLoss;
|
|
}
|
|
else if (repairLevel == "repair_rebuild")
|
|
{
|
|
recommenedRepairLevel = DatabaseTable.RepairLevel.Rebuild;
|
|
}
|
|
|
|
if (!_errorsDictionary.ContainsKey(objectId))
|
|
{
|
|
_errorsDictionary.Add(objectId, recommenedRepairLevel);
|
|
}
|
|
else
|
|
{
|
|
_errorsDictionary[objectId] = recommenedRepairLevel < _errorsDictionary[objectId]
|
|
? recommenedRepairLevel
|
|
: _errorsDictionary[objectId];
|
|
}
|
|
}
|
|
}
|
|
|
|
foreach (var table in _databaseObjectsDictionary)
|
|
{
|
|
if (table.Value.TypeOfTable == DatabaseTable.TableType.UserTable)
|
|
{
|
|
userTablesListBox.Items.Add(table.Value.FullMessage);
|
|
}
|
|
else
|
|
{
|
|
systemTablesListBox.Items.Add(table.Value.FullMessage);
|
|
}
|
|
|
|
if (table.Value.RecommenedRepairLevel != DatabaseTable.RepairLevel.None)
|
|
{
|
|
var item = new ListViewItem
|
|
{
|
|
Text = $@"{table.Value.Name} has corruption, recommended repair option: {table.Value.RecommenedRepairLevel}."
|
|
};
|
|
damagedTablesListView.Items.Add(item);
|
|
}
|
|
}
|
|
//Set the main message for the user.
|
|
if (damagedTablesListView.Items.Count == 0)
|
|
{
|
|
dbCcResultLabel.Text = @"No errors in the database have been detected.";
|
|
repairButton.Enabled = false;
|
|
}
|
|
else
|
|
{
|
|
dbCcResultLabel.Text = @"One or more errors have been found, refer to the 'Damaged Tables' section for details.";
|
|
repairButton.Enabled = true;
|
|
}
|
|
}
|
|
catch (SqlException e)
|
|
{
|
|
MessageBox.Show(e.Message);
|
|
}
|
|
}
|
|
|
|
//https://gallery.technet.microsoft.com/scriptcenter/59bc1fb6-68de-4624-b338-83dd5461da25
|
|
private static DatabaseDbCcResult ParseDbCcResultMessage(string message)
|
|
{
|
|
//0 1 2 3 4 5 6 7 8 9 10 11
|
|
//CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdvertisingProfitControl'.
|
|
//CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysowners'(object ID 27).
|
|
//NOTE: In the event of errors being found, multiple CHECKDB messages can be found.
|
|
var connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
|
|
if (message == string.Empty) return new DatabaseDbCcResult();
|
|
|
|
var textArray = message.Split(' ');
|
|
var allocationErrors = int.Parse(textArray[2]);
|
|
var consistencyErrors = int.Parse(textArray[6]);
|
|
var tableName = textArray[11].Replace("'", ""); //Remove all quotes.
|
|
tableName = tableName.Remove(tableName.Length - 1); //Strip out the period.
|
|
var objectId = 0;
|
|
if (tableName != connection.Database)
|
|
{
|
|
//This indicates that a specific table has an error in it.
|
|
ParseTableObjectIdNumber(tableName, out objectId, out tableName);
|
|
}
|
|
var dbCc = new DatabaseDbCcResult
|
|
{
|
|
AllocationErrorCount = allocationErrors,
|
|
ConsistencyErrorCount = consistencyErrors,
|
|
FullMessage = message,
|
|
AffectedTableId = objectId
|
|
};
|
|
return dbCc;
|
|
}
|
|
|
|
private static DatabaseTable ParseDatabaseTableMessage(string message)
|
|
{
|
|
//0 1 2 3 4 5 6 7 8 9
|
|
//There are 1271 rows in 17 pages for object "sys.sysrscols".
|
|
//There are 425 rows in 6 pages for object "ActualSales".
|
|
var tempText = message.Split(' ');
|
|
var tableDetails = new DatabaseTable();
|
|
var rowCount = int.Parse(tempText[2]);
|
|
var pageCount = int.Parse(tempText[5]);
|
|
var tableName = tempText[9].Replace("\"", "");//Remove all quotes.
|
|
tableName = tableName.Remove(tableName.Length - 1);//Strip out the period.
|
|
|
|
tableDetails.TypeOfTable = tableName.StartsWith("sys") ? DatabaseTable.TableType.SystemTable : DatabaseTable.TableType.UserTable;
|
|
tableDetails.RowCount = rowCount;
|
|
tableDetails.PageCount = pageCount;
|
|
tableDetails.FullMessage = message;
|
|
tableDetails.Name = tableName;
|
|
|
|
return tableDetails;
|
|
}
|
|
|
|
private static void ParseTableObjectIdNumber(string text, out int objectId, out string tableName)
|
|
{
|
|
//FORMAT IN: sys.sysowners(object ID 27)
|
|
var tempText = text.Split('('); //tempText[0] = sys.sysowners | tempText[1] = object ID 27)
|
|
tableName = tempText[0];
|
|
tempText = tempText[1].Split(' '); //tempText[2] = 27)
|
|
tempText[0] = tempText[2].Remove(tempText[2].Length - 1); //tempText[0] = 27
|
|
objectId = int.Parse(tempText[0]);
|
|
}
|
|
//Reference for DBCC operations: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
|
|
public enum DatabaseObjectType
|
|
{
|
|
Table = 2593, //A database table object has this value.
|
|
DbCcResult = 8989, //This value indicates that DBCHECK has printed a summary statement.
|
|
ServiceBrokerMessage = 8997, //This value indicated that the message text was made by a service broker.
|
|
Unkown = 0
|
|
}
|
|
//
|
|
internal class DatabaseTable
|
|
{
|
|
public string Name;
|
|
public int Id;
|
|
public int Error; //Error seems to indicate the objects category int the database and isn't a unique number.
|
|
public int RowCount;
|
|
public int PageCount;
|
|
public string FullMessage;
|
|
public TableType TypeOfTable = TableType.Unkown;
|
|
public RepairLevel RecommenedRepairLevel = RepairLevel.None;
|
|
|
|
public enum TableType
|
|
{
|
|
UserTable = 0,
|
|
SystemTable = 1, //Note: Internal_Tables will be counted as system tables.
|
|
Unkown = 2
|
|
}
|
|
|
|
//Zero is the most sever repair option with possible data loss.
|
|
public enum RepairLevel
|
|
{
|
|
AllowDataLoss = 0,
|
|
Rebuild = 1,
|
|
None = 2
|
|
}
|
|
}
|
|
|
|
internal class DatabaseDbCcResult
|
|
{
|
|
private int _allocationErrorsCount;
|
|
private int _consistencyErrorsCount;
|
|
public string FullMessage;
|
|
public int AffectedTableId;
|
|
|
|
public int AllocationErrorCount
|
|
{
|
|
get { return _allocationErrorsCount; }
|
|
set
|
|
{
|
|
HasErrors = value != 0;
|
|
_allocationErrorsCount = value;
|
|
}
|
|
}
|
|
|
|
public int ConsistencyErrorCount
|
|
{
|
|
get { return _consistencyErrorsCount; }
|
|
set
|
|
{
|
|
HasErrors = value != 0;
|
|
_consistencyErrorsCount = value;
|
|
}
|
|
}
|
|
|
|
public bool HasErrors { get; private set; }
|
|
}
|
|
|
|
private void repairButton_Click(object sender, EventArgs e)
|
|
{
|
|
//var allowDataLossOnAll = false;
|
|
foreach (var repairLevel in _errorsDictionary)
|
|
{
|
|
var s = _databaseObjectsDictionary[repairLevel.Key];
|
|
var repairOption = string.Empty;
|
|
if (s.RecommenedRepairLevel == DatabaseTable.RepairLevel.AllowDataLoss)
|
|
{
|
|
repairOption = "repair_allow_data_loss";
|
|
}
|
|
else if (s.RecommenedRepairLevel == DatabaseTable.RepairLevel.Rebuild)
|
|
{
|
|
repairOption = "repair_rebuild";
|
|
}
|
|
var query = $"USE AdvertisingProfitControl\r\nALTER DATABASE AdvertisingProfitControl\r\nSET SINGLE_USER\r\nWITH ROLLBACK IMMEDIATE;\r\ndbcc checktable (\'{s.Name}\', {repairOption})\r\nALTER DATABASE AdvertisingProfitControl\r\nSET MULTI_USER;";
|
|
using (var connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
|
|
{
|
|
using (var command = new SqlCommand(query, connection))
|
|
{
|
|
connection.Open();
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
} |