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(); } } } }