/// <summary>
/// Populates the report data regarding the current size and disk space allocation of the current database.
/// </summary>
/// <param name="report">The report object to populate.</param>
public void GetSpaceUsed(ref DatabaseReport report)
{
if (report == null)
{
report = new DatabaseReport();
}
using (var connection = new SqlConnection(this.connectionStringSettings.ConnectionString))
{
var command = new SqlCommand(SpaceUsedQuery, connection);
try
{
connection.Open();
var reader = command.ExecuteReader();
report = new DatabaseReport();
while (reader.HasRows)
{
// the space used stored procedure only returns one row per result set
if (reader.Read())
{
if (reader.GetName(0).Equals("database_name", StringComparison.InvariantCultureIgnoreCase) && reader.FieldCount >= 3)
{
report.DatabaseName = reader.GetString(0);
report.DatabaseSize = reader.GetString(1);
report.UnallocatedSpace = reader.GetString(2);
}
else if (reader.GetName(0).Equals("reserved", StringComparison.InvariantCultureIgnoreCase) && reader.FieldCount >= 4)
{
report.Reserved = reader.GetString(0);
report.Data = reader.GetString(1);
report.IndexSize = reader.GetString(2);
report.UnusedData = reader.GetString(3);
}
}
reader.NextResult();
}
}
catch (SqlException exception)
{
Log.Error("Shrink: SqlException during querying for space used", exception, this);
}
}
}
/// <summary>
/// Populates the report data regarding the blob sizes within the current database.
/// </summary>
/// <param name="report">The report object to populate.</param>
public void GetOrphanedBlobsSize(ref DatabaseReport report)
{
if (report == null)
{
report = new DatabaseReport();
}
using (var connection = new SqlConnection(this.connectionStringSettings.ConnectionString))
{
var command = new SqlCommand(BlobsReportQuery, connection);
try
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.HasRows)
{
// the blobs report query only returns one row per result set
if (reader.Read())
{
// if there is no unused data, the reader with return a SQL null value, which cannot be assigned to a decimal
if (!reader.IsDBNull(0))
{
// the result contains two result set, so we're detecting which result set this is by the first column name
if (reader.GetName(0).Equals("usedBlobs", StringComparison.InvariantCultureIgnoreCase))
{
report.UsedBlobsSizeInBytes = reader.GetInt64(0);
}
else if (reader.GetName(0).Equals("unusedBlobs", StringComparison.InvariantCultureIgnoreCase))
{
report.UnusedBlobsSizeInBytes = reader.GetInt64(0);
}
}
}
reader.NextResult();
}
}
catch (SqlException exception)
{
Log.Error("Shrink: SqlException during querying for the blob sizes", exception, this);
}
}
}
