Setup SQL Email Alert for Disk Space Usage

I had an application go kaput on me all of a sudden and that wasn’t good. I had gotten back from lunch (always happens when I get back from lunch) and was immediately approached by the Sys Admin saying that a certain web application couldn’t connect to the SQL database. He wanted me to check out why and get back to him ASAP.

So I quickly log into the database server and see that SQL server is running fine, the SQL account that the web application uses is not “locked” and the password expiration is not checked (meaning that technically the password for that SQL account should never expire). I look at the disk drives by clicking on “My Computer” in Windows Explorer and quickly saw …

“E: Drive 0 GB free of 99 GB”

We have SQL Server configured to store Audit files on the “E” drive. I went into the folder and removed the oldest month worth of audit files. That freed up about 20 GB of space.

Roughly 5 minutes later the same Sys Admin ran in my office saying, “hey! I reset the SQL Account password and it works now.”


I know. What a coincidence right? :)

I believe the reason why the application couldn’t connect to the database is because SQL Server couldn’t write any more audit files to the disk drive. As a result, SQL Server refused outside connections.

I wrote a script that checks whether a specific disk drive space goes below a given threshold. If so, it will send the DBAs an email alert.

Here is the script. Feel free to use/modify it however you see fit.

--create temp table for results
create table #freespace
(drive char(1),mb_free int)

--insert drive data into temp table
insert into #freespace exec sys.xp_fixeddrives

declare @subject varchar(100)
declare @profile_name varchar(25)
declare @body varchar(200)
declare @gb_free int
declare @recipients varchar(50)

--you can specify whatever drive leter
select @gb_free = (mb_free / 1024) from #freespace where drive = 'E'

--you can specify whatever number you want. I put 20GB.
if (@gb_free < 20)
  SET @profile_name = 'Profile Name goes here'
  SET @recipients = ''
  SET @subject = 'ALERT!! E: drive is BELOW 20 GB'
  SET @body = 'Please check the E: drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(varchar,@gb_free) + ' GB of free space left on the E drive.'
  exec msdb.dbo.sp_send_dbmail
  @profile_name = @profile_name,
  @body = @body,
  @subject = @subject,
  @recipients = @recipients
--drop temp table
drop table #freespace;

Calculate Database Compression Ratio

Here’s a quick and easy formula to help show upper management how much your backup compression strategy is saving the company.

Let’s assume your original non-compressed SQL backup file is 100MB. After you enabled compression your compressed backup size is now 5MB. Plug in the numbers below and you get:

Percent decrease = (OriginalSizeofDB – CompressedSizeofDB) ÷ OriginalSizeofDB.

= (100 – 5) ÷ 100

= .95 = 95%

So in the above scenario by simply enabling database backup compression, you saved 95% of disk space per backup file. Neat!

SQL Server Database Instance-Level Backup Compression Setup

I was fortunate enough to attend Paul Randal’s and Kimberly Tripp’s IETPO1 this past Spring. During the week long training I met Tim Radney (he’s a SQL Consultant at SQLSkills). I approached him, introduced myself and as we were talking, the subject of SQL Server backups came up. I explained my work’s current backup strategy and how I’d like to make it more efficient, both in speed and disk space. Tim suggested I enable the instance-wide backup compression option in SQL Server Management Studio (see image below)

Since then, I have checked that option on all my database servers. In some cases it has compressed the backup file size by 80%. How neat is that!

How to Enable SQL Instance Backup Compression in SQL Server Management Studio

Right click the Instance, click Properties, click “Database Settings” on the left, and make sure there’s a check mark in  “Compress backup” check box. Done.

SQL Server Instance Wide Compression

SQL Server Email Alerts Setup

Recently at work we had an issue where a SQL login account’s password expired and the application that uses that login stopped working. The Window’s password policy for our organization requires passwords to expire after 60 days.

Management requested that we get “an email when the password is about to expire so that we can change it to prevent the application from not being able to connect.”

So I created a little script that runs daily (via a SQL Job) to check all SQL Server login accounts for passwords that will expire in ’10’ (you can change the number to whatever you want) days or less. If any results are found, it will generate and send an email with the SQL login as a reminder that you have “X” number of days until the password expires.

Below is the script. Feel free to use/modify it for your specific needs.

USE master;

DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
DECLARE @SQLAccountUser as varchar(25)
DECLARE @DaysToExpire as varchar(10) = '10'   -- you can pass this number as a parameter when calling this SP.
--Declare Cursor and get all SQL Accounts that have 'Password Expiration' enabled.
select name 
from sys.sql_logins
where is_disabled = 0 and is_expiration_checked = 1 and (
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) > 0 and
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) <= @DaysToExpire )
--Open the cursor
OPEN get_SQLAccount;
FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser; 
    SET @p_profile_name = N'Your Profile Name'
    SET @p_recipients = N''
  SET @p_subject = N'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  SET @p_body = 'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @p_profile_name,
     @recipients = @p_recipients,
     @body = @p_body,
     @body_format = 'HTML',
     @subject = @p_subject
  FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser;
CLOSE get_SQLAccount;

With All Due Respect…

Recently at work, I came back from lunch to my coworkers yelling,

We’ve been looking for you! You need to restore the database!

I was extremely surprised and caught off-guard by the verbal attack. I tried my best to keep my cool, even though my heart was racing fast, and asked for further explanation as to why I should “restore the database?”

They replied, “We can’t connect to our application!

After some reflection I came up with two issues I had with what happened that day:

1. The fact that this application was an internal reporting software application with NO outside client connections. Despite this, they threw professionalism and common respect out the window by ganging up on me. Now, don’t get me wrong. I do understand their frustration…but still people should have basic common respect in how they speak to other people.

2. By yelling, “restore the database!” they belittled my intelligence as a SQL DBA. Telling me what the problem is can be a little insulting. It’s like me going to a doctor and tell him what my problem is.

After further troubleshooting, I figured out the reason why their application was not connecting to SQL Server was due to an expired SQL Account password. I reset the password and the application came online. No “restore the database” needed.

The “takeaway” of this blog post is be comfortable in your duties in whatever you do. Be confident. Be proud. Be humble. Always seek to increase your understanding and knowledge in whatever area you work in. Don’t worry when people (who don’t do what you do) yell at you and tell you how to do your job.

Keep your calm.

People You Meet Along The Way

There are always people you come across in life that stand out from others. They are the ones that end up becoming life long friends.

In terms of my professional career, there are two people that I credit for being a DBA and starting this blog.

One of them is an ex-coworker of mine I met in 2012. I remember meeting him for the first time. He was the Senior DBA and extremely helpful. One day he asked where I see my career going in five years and I told him I’d like to get into “programming.” He advised that I try out database administration. I ended up working with him for the next 2-3 years and I thank him to this day for suggesting that I pursue the path of a SQL Server DBA. I can’t express how content I am to have taken his advice.

The other person that played an important role in the direction my career is going is Tim Radney. Tim is a Principal Consultant with SQLSkills and was at the IEPTO1: Immersion Event on Performance Tuning and Optimization Training I took earlier this year (read the review here). Even though he wasn’t training the course, he took the time in between breaks and lunch to answer my random SQL questions. He advised I start a blog and work on making a name for myself. I took his advice to heart and this blog is a testament to that. If it wasn’t for his suggestion to start this blog, I probably wouldn’t have done it.

My tip to any upcoming professional is that positive change cannot take place unless it’s rooted in humility. It is extremely easy to think that you know everything and you don’t need anyone’s help. That mentality is probably one of the biggest roadblocks to growing as an individual both in your personal and professional life. Be humble. Be open.

Blogging Baby Steps

Looking back, I don’t remember a time that I ever liked to write. I was the kid in school that always took forever to write a paper because it was “never good enough.”

With some encouragement from my friend Tim Radney, I decided to take the plunge and travel down the road of blogging. I do work full time as a SQL Server DBA so most of my posts will be centered around things I learn everyday at work.

Like the title of this blog says, I’m starting out with “baby steps” but hopefully I’ll be up and running very soon!