How can we restrict tempdb usage for specific users or queries on SQL Server?

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run from the front end, they sometimes mess up with the date range which pulls millions of rows to temp tables. Yes, the users need to be educated and also tuning is required, which we have done, but still occasionally they are creating problems.

And now they say that in SAP ASE (Sybase) there is a feature to create multiple tempdb's and redirect users and restrict usage, by not bloating up the single tempdb (as like in SQL Server) and bring the server down.

Do we have anything in SQL Server like this?

As you mentioned, users need to be educated, and queries need a rewrite to be more efficient to use less of tempdb.

SQL Server does not have a concept of multiple tempdb, nor can you restrict a user by how much space they can use from tempdb.

One solution will work, and it is not the best. Monitor tempdb usage by each session and make a decision based on the amount. You can decide to terminate a session, but you need to consider the business impact? Do you suggest they rerun at a later time when the server has less activity? How long can a business wait to rerun before it impacts the bottom line of the business?

A few suggestions in Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server? this Q&A if you decide to chose this route.

StackExchange Database Administrators Q#294631, answer score: 5