public static void SqlDbFileSize()
{
DataSet sqlinstances = GetSQLInstances.GetSQLDatabases();
StringBuilder errorMessages = new StringBuilder();
foreach (DataTable table in sqlinstances.Tables)
{
foreach (DataRow row in table.Rows)
{
var sqlcon = "Data Source = " + row["sqlinstance"].ToString() + "; Initial Catalog = " + row["database"] + "; Integrated Security = SSPI";
using (var sqlmon = new SqlConnection(sqlcon))
{
var databaseFileSize = new DataSet();
string query = @"SELECT
SUM(CONVERT(BIGINT,CASE WHEN df.type = 0 THEN CAST(FILEPROPERTY(df.[name], 'SpaceUsed') AS bigint) * 8 * 1024 ELSE 0 end)) [TotalDataUsedBytes],
SUM(CONVERT(BIGINT,CASE WHEN df.type = 1 THEN CAST(FILEPROPERTY(df.[name], 'SpaceUsed') AS bigint) * 8 * 1024 ELSE 0 end)) [TotalLogUsedBytes],
SUM(CONVERT(BIGINT,CASE WHEN df.type = 0 THEN CAST(df.size AS bigint) * 8 * 1024 ELSE 0 end)) [TotalDataSizeBytes],
SUM(CONVERT(BIGINT,CASE WHEN df.type = 1 THEN CAST(df.size AS bigint) * 8 * 1024 ELSE 0 end)) [TotalLogSizeBytes]
FROM
sys.database_files df;" ;
try
{
var SQLInstanceDA = new SqlDataAdapter(query, sqlmon);
SQLInstanceDA.Fill(databaseFileSize);
foreach (DataTable fileSizeTable in databaseFileSize.Tables)
{
foreach (DataRow fileSizeRow in fileSizeTable.Rows)
{
SQLMonitorUpdateDB.UpdateDBFileSizes((int)row["instanceid"], (int)row["databaseid"], (Int64)fileSizeRow["TotalDataUsedBytes"],
(Int64)fileSizeRow["TotalLogUsedBytes"], (Int64)fileSizeRow["TotalDataSizeBytes"], (Int64)fileSizeRow["TotalDataSizeBytes"]);
}
}
SQLInstanceDA.Dispose();
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(row["sqlinstance"].ToString() + " " + row["database"].ToString() + " " + errorMessages);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex);
}
databaseFileSize.Dispose();
}
}
}
}
public static void GetInstanceStats()
{
DataSet sqlinstances = GetSQLInstances.SQLInstances();
foreach (DataTable table in sqlinstances.Tables)
{
foreach (DataRow row in table.Rows)
{
long ple = PageLifeExpectancy.GetPageLifeExpectancy(row["name"].ToString());
WaitType.GetWaitStats(row["name"].ToString(), (int)row["Id"]);
SQLMonitorUpdateDB.UpdateInstancestats((int)row["id"], ple);
}
}
}
public static void GetMachineStats()
{
DataSet servers = GetSQLInstances.GetPhysicalMachines();
foreach (DataTable table in servers.Tables)
{
foreach (DataRow row in table.Rows)
{
double cpu = GetMachineCPU(row["physicalServerName"].ToString());
double ram = GetMachineRAM(row["physicalServerName"].ToString());
double diskIO = GetDiskReads(row["physicalServerName"].ToString());
SQLMonitorUpdateDB.UpdateServerStats(row["physicalServerName"].ToString(), cpu, ram, diskIO);
}
}
}
public static void SqlLogBackupLastRun()
{
DataSet sqlinstances = GetSQLInstances.SQLInstances();
StringBuilder errorMessages = new StringBuilder();
foreach (DataTable table in sqlinstances.Tables)
{
foreach (DataRow row in table.Rows)
{
var insid = (int)row["id"];
var sqlcon = "Data Source = " + row["name"].ToString() + "; Initial Catalog = master; Integrated Security = SSPI";
using (var sqlmon = new SqlConnection(sqlcon))
{
var logBackupDs = new DataSet();
string query = @"WITH cte AS (
SELECT
bs.database_name,
bs.type,
bs.backup_size,
bs.backup_start_date,
bs.backup_finish_date,
bms.is_password_protected,
bmf.physical_device_name,
CASE
WHEN bmf.device_type = 2 THEN 1
ELSE 0
END AS is_native_backup,
bmf.logical_device_name,
ROW_NUMBER() OVER (
PARTITION BY bs.database_name,
bs.type
ORDER BY
bs.backup_finish_date DESC
) [order_id]
FROM
msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediaset bms ON bms.media_set_id = bs.media_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE
DB_ID(bs.database_name) IS NOT NULL
)
SELECT
database_name,
backup_finish_date
FROM
cte
WHERE
cte.order_id = 1 --and type = 'D'
and backup_finish_date = (
SELECT
MAX(backup_finish_date)
FROM
cte cte2
WHERE
cte.database_name = cte2.database_name
and type = 'L'
)
ORDER BY
database_name";
try
{
var SQLInstanceDA = new SqlDataAdapter(query, sqlmon);
SQLInstanceDA.Fill(logBackupDs);
foreach (DataTable logBKtable in logBackupDs.Tables)
{
foreach (DataRow logBKrow in logBKtable.Rows)
{
SQLMonitorUpdateDB.UpdateBackupHistory((int)row["id"], logBKrow["database_name"].ToString(), (DateTime)logBKrow["backup_finish_date"], "L");
}
}
SQLInstanceDA.Dispose();
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(errorMessages);
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex);
}
logBackupDs.Dispose();
}
}
}
}