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 = 'firstname.lastname@example.org', @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:
Use these rules to stay connected to your SQL Server and look like a pro!