SQL Server Alerts via Text Message

After a very mild winter thus far, we’re finally getting some cold weather. Today was -1 degree as I left for work this morning. It looks like we could finally get some ice that will be safe for fishing. I’ve never been a huge fan of ice fishing, but I have to admit I’m growing rather restless this winter. Ice fishing is another sport that is expensive to get into. An auger will cost at least $300, then you’ll want some poles for jigging (about $30 each), lures, a shanty so you don’t freeze, a heater for the shanty, and plenty of super warm clothes and outerwear. All told, if you’re looking to start the sport, you’re looking at about $1,000. The best thing to do is find some friends who enjoy it and tag along. Then you’ll at least get the experience and decide if you want to start investing in some of the equipment.
It is important for a DBA to stay connected with his/her system. You have to really know what you have to know if it is performing well, poorly, or at all. One way to stay connected is through alerts. SQL Server is able to send emails through DatabaseMail to warn when things aren’t working properly. This works well, but if you don’t want to have to be constantly checking your email to see problems, you can also send text messages to your phone. This is especially handy for emergency situations, like running out of space. Hopefully you’d know well ahead of time if you are running out of space, but crazy things can happen and you have to be ready for them.
Let’s say you know your tempdb database data files are using the entire LUN that they are on – no file growth is possible. If the applications using your database max out the space inside tempdb, your database is going to crash. You’re a good DBA, so you’re constantly monitoring tempdb used space with this table and stored procedure. A SQL agent job calls the stored procedure every five minutes.

CREATE TABLE [dbo].[tempdbSpaceMonitoring](
   [TID] [INT] IDENTITY(1,1) NOT NULL,
   [Filename] [VARCHAR](50) NOT NULL,
   [FileSize_MB] [FLOAT] NOT NULL,
   [UsedSpace_MB] [FLOAT] NOT NULL,
   [UserObjectUsed_MB] [FLOAT] NULL,
   [InternalObjectUsed_MB] [FLOAT] NULL,
   [VersionStoreUsed_MB] [FLOAT] NULL,
   [IsLog] [BIT] NOT NULL,
   [CaptureDateTime] [DATETIME] NOT NULL,
 CONSTRAINT [PK_tempdbSpaceMonitoring] PRIMARY KEY CLUSTERED 
(
   [TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE PROCEDURE [dbo].[CapturetempdbSpaceMonitoring] 

AS
BEGIN
   
   DECLARE @CaptureDate DATETIME
   SELECT @CaptureDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30 - DATEPART(SECOND, GETDATE() + '00:00:30.000'), GETDATE())), 0)
   
   INSERT INTO dbo.tempdbSpaceMonitoring
       (   Filename,
           FileSize_MB,
           Usedspace_MB,
           UserObjectUsed_MB,
           InternalObjectUsed_MB,
           VersionStoreUsed_MB,
           IsLog,
           CaptureDateTime)
   SELECT
           df.name,
           df.size/128,
           df.size/128 - fsu.unallocated_extent_page_count/128,
           fsu.user_object_reserved_page_count/128,
           fsu.internal_object_reserved_page_count/128,
           fsu.version_store_reserved_page_count/128,
           0,
           @CaptureDate
       FROM tempdb.sys.dm_db_file_space_usage fsu
       INNER JOIN tempdb.sys.database_files df ON fsu.FILE_ID = df.FILE_ID
   UNION
   SELECT  f.name,
           f.size,
           os.cntr_value/8/128 AS freespace,
           NULL,
           NULL,
           NULL,
           1,
           @CaptureDate
       FROM tempdb.sys.dm_os_performance_counters os
       FULL OUTER JOIN (SELECT name,size/128 AS size FROM tempdb.sys.database_files WHERE TYPE = 1) AS f ON 1=1
       WHERE counter_name = 'Log File(s) Used Size (KB)'
       AND instance_name = 'tempdb'
       
END

GO

You’d want an email sent whenever the percent of available space goes under 5%. You can get the percent of space used with the following SQL:

SELECT  SUM(FileSize_MB) AS FileSize_MB ,
        SUM(UsedSpace_MB) AS UsedSpace_MB ,
        CaptureDateTime
FROM    DBMaint.dbo.tempdbSpaceMonitoring
WHERE   IsLog = 0
GROUP BY CaptureDateTime
ORDER BY CaptureDateTime DESC;

So I’d set up a SQL Agent job to run every five minutes and execute this SQL:

DECLARE @percentused DECIMAL(3, 2);

SELECT TOP 1
@percentused = SUM(UsedSpace_MB) / SUM(FileSize_MB) * 100
FROM    DBMaint.dbo.tempdbSpaceMonitoring
WHERE   IsLog = 0
GROUP BY CaptureDateTime
ORDER BY CaptureDateTime DESC;

IF @percentused > 95
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = '5555555555@email.uscc.net',
@subject = N'TempDB Almost Full',
@body = N'TempDB is almost full.  Please login and check for unclosed connections';
END;

The text message is sent just like email, except you have to put the number into the Send To address. There is some additional characters needed depending on which cell carrier you use. The additional characters can be found on this website.

Here are the sample results:

20160113 Text alert

Use these rules to stay connected to your SQL Server and look like a pro!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s