DBA responsibility is to monitor the SQL Server Storage. There are multiple ways to monitor the storage space.
Manual task:
One of my friend from the DBA team he uses to see the storage manually. Once he reached office he uses to connect to the server system and open the computer and see the free spaces of the storage.
Like below
He comes to know that in C drive we have 30 GB free Space and H 10 GB like. After that, he writes an alert mail to the respective team.
Automation
I automate this task now DBA team getting mail to his inbox daily Morning like below.
Let’s see how we will achieve this task.
I am creating a package in that I extracting the storage status and sending mail to the DBA team.
xp_fixeddrives
With the help of this system SP, we will get the information about the free spaces.
See below.
I am creating a package in that I have created some variable.
Free_Space is an object type variable in which we are storing the result set of the System Sp.
Taking Execute SQL task
And configuring the connection manager.
In result set table we need to configure the
Click ok.
Now I am taking script take to compose the mail and send it to DBA.
Here taking variable
Click ok Edit script.
In the script task, I wrote below code
public void Main()
{
// TODO: Add your code here
string smtpAddress = "smtp.gmail.com";
int portNumber = 587;
bool enableSSL = true;
string emailFromAddress = Dts.Variables["User::Email_id_sender"].Value.ToString(); //Sender Email Address
string password = Dts.Variables["User::Mail_credentials"].Value.ToString(); ; //Sender Password
string emailToAddress = Dts.Variables["User::Email_id_reciver"].Value.ToString(); //Receiver Email Address
string subject = "Drive Free Alert";
string body = "Hello Team, Server Storage Information in GB.";
DataTable dt = new DataTable();
var oleDa = new OleDbDataAdapter();
float f;
oleDa.Fill(dt, Dts.Variables["User::Free_Space"].Value);
using (MailMessage mail = new MailMessage())
{
mail.From = new MailAddress(emailFromAddress);
mail.To.Add(emailToAddress);
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = true;
mail.Body += " <html>";
mail.Body += "<body>";
mail.Body += "<table border=2>";
mail.Body += "<tr>";
mail.Body += "<td bgcolor=#00FFFF>Drive Name</td><td bgcolor=#00FFFF>Free Space in GB</td>";
mail.Body += "</tr>";
foreach (DataRow row in dt.Rows)
{
mail.Body += "<tr>";
mail.Body += "<td>" + row.ItemArray[0].ToString() + "</td>";
f = float.Parse(row.ItemArray[1].ToString()) / 1024;
if(f<10)
mail.Body += "<td bgcolor=#FF0000>" +f + " GB" + "</td>";
else
mail.Body += "<td bgcolor=#00FF00>" + f+ " GB" + "</td>";
mail.Body += "</tr>";
}
mail.Body += "</table>";
mail.Body += "</body>";
mail.Body += "</html>";
using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new NetworkCredential(emailFromAddress, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
|
Business logic is that if the free space is less than 10 GB record show in Red otherwise Records Show in green.
Build the code and save and close it.
Now the package is ready to run
Now running this package
Before running see the free space in the system.
Running this package.
See the mail
The package is ready to use. We can schedule this package in any scheduler.
Hope this article is helpful for the DBA team.
Thanks!!!
