Monitoring tempdb space

hemismith

Please excuse me as I am not a DBA. I am responsible for a data warehouse, and our production system has gone "down" periodically due to tempdb filling up. I asked our DBAs to add me to the low space alert emails and received the following response:

From SQL Monitor: "This alert is disabled by default on [master], [model], [msdb] and [tempdb] as they are generally MBs in size and in most scenarios don't grow significantly, and on log files and [tempdb] as their growth patterns are not typically predictable based on historic growth." So, I cannot add you to the alert.

I see two types of alerts configured: the standard "Disk space" for which notifications are enabled, and a custom alert "Database free space below the preferred threshold" for which notifications are not enabled. I'm not sure what the quoted text is referring to but I would assume the standard alert. I see several alerts triggered in the past for the 2nd (custom) alert, including some for msdb; I see none for the standard disk space alert.

Our tempdb sits on a dedicated 768GB flash drive, although it is not currently configured to fill up the entire disk.

Questions: Can alerts be configured for tempdb? If so, which type of alert would be the best?

Russell D

You can enable the database file usage alert on temp db if you want to, you just need to navigate to the database level in the alert configuration tree but options are limited right now. However, we're currently in the process of doing a large piece of work for better tempdb monitoring which should hopefully improve your situation.

hemismith

Great, thank you.