Tell Me When tempdb Is Low On Space

Dave Mason shows how to configure alerts to fire when tempdb is low on disk space:

Naturally, the job runs on a predefined schedule. But how frequently should we check disk/available space for [tempdb]? The temporary nature of [tempdb] makes this a difficult question: objects within aren’t saved from one session of SQL Server to another, and evidence to explain runaway growth or loss of available space may be gone before an assessment can be made. Whatever schedule I decide on, I’ll always wonder if it’s frequent enough (or too frequent).

It’s tempting to “over-schedule” a job’s frequency, perhaps as much as every X seconds. Asking SQL Server “Are we out of disk space?” over and over again doesn’t make a lot of sense to me, though. It reminds me of Bart and Lisa asking Homer “Are we there yet?”until he snaps. Ideally, instead of asking, I want SQL Server to *tell me* when disk/available space is running low.

Read on to see how Dave does this.

Related Posts

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

What sys.dm_exec_query_stats Can Miss

Matthew McGiffen takes us through a scenario where sys.dm_exec_query_stats did not give a complete view of what was running on SQL Server: I got less than 50 rows back so figured I had everything covered, but the total elapsed time across all the queries was less than ten minutes, I knew the server hadn’t been […]

Read More

Categories

June 2018
MTWTFSS
« May Jul »
 123
45678910
11121314151617
18192021222324
252627282930