In high OLTP nature environment we have seen many times that tempdb gets full due to bad query run by the user and it affects entire server as tempb is common area for sorting user data in the database server.

To overcome this issue, we can have another user defined tempdb and assign hot batch logins to that tempdb, so it doesn't impact original tempdb and database processing can continue for all users.

create temporary database tempdb_usr on tempdev='2000M'
sp_tempdb 'bind','lg','<login_name>','db','tempdb_usr'

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.