Limit TempDB tables "by user"

Question

Limit TempDB tables "by user"

Raul Black

Old Hand

Points: 303
More actions
October 20, 2006 at 4:52 pm

#62825

I'm using Sql Server 2000, and I have the TempDB recovery mode set to simple. However, a user ran a query that grew TempDB to 80GB. This resulted in running the drive out of space.
Is there a way to limit the size a temp table is allowed to grow depending on who the user is?
Thank you,
rb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

Those setting are database wide setting and cannot be set for user.

If you are aware of the query which is causing it to grow 80 GB you may want to look at the query and check why tempdb is being used drastically.

You can restrict the size of tempdb to some extreme limit and let the users query fail

jon ness Valued Member Points: 59 More actions · Answer 2

Hi:

Thanks for your reply. We have a data warehouse that is accessible to many users. Thus the database being used was read only. However, the users can still create temporary tables in tempdb.

The problem with limiting tempdb for everybody is that we sometimes need to use a lot of space in tempdb ourselves. I've been trying to find a way to dynamically limit users' use of tempdb while allowing our own.

We could impose a maximum limit that does not allow tempdb to grow beyond a certain size (and probably will), but I am hoping to find a more granular solution.

Thank you,

Raul

John Rowan SSC Guru Points: 56440 More actions · Answer 3

This is probably not the answer that you want, but it sounds like you need more disk space. If users are allowed to create and use temporary tables in a data warehouse environment, tempDB usage is bound to be large. I would increase disk space for your tempDB (make sure it is on its own disk/RAID set). You may also want to create standards for temporary table usage. Educate your users on when and how to use temporary tables and stress to them that they need to drop the tables as soon as possible so they are not holding open resources in TempDB.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 4

Don't forget, also, that tempdb is used by the optimizer for creating intermediate join tables, sort tables, hash tables etc whilst a query is executing, even if that query does not explicitly require user #temp tables.

I had an incident last week where a user kicked off an admittedly very large and complex query on our data warehouse. I got called in the night to say that they had "filled up all the disk space". The user database was read-only, but tempdb data filegroup had grown to 106 Gb! This was entirely due to the intermediate worktables created by the optimizer in order to handle the complexity of the joins in the user query.