Tempdb file run out of space

Question

Tempdb file run out of space

sqlfriend

SSC Guru

Points: 52794
More actions
March 7, 2012 at 12:42 pm

#269585

When one of the junior developers running a query, he got an error:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I noticed the tempdb has been grown into 20 GB, and I as a DBA set tup the restriction to 25 gb earlier for the file.
Is it normal for a tempdb to gets so big? I can set it up to unlimited restrictions, but I just don't like they made some kind of queries without any optimization and almost run out of disk space, I did last week move the tempdb to a separate drive, but it seems it still is growing.
What should I do next step?
thanks

Viewing 8 posts - 1 through 8 (of 8 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

The DBA in me says to keep it restricted. Then whenever they have one of these queries run amok you will find out about it.

Is this a dev server or a prod server?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

I think the person runs it knows which query it is.

The server is for a research and evalution department, they only have one server there. and each user has a sandbox database called for exampler workKathy, workJohn, also with a production database that restored from another server, and along with some archived databases from the prodcution databases.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

That's all fine and well that the person running it might know what they are doing. But if they don't let you evaluate the query and tune it how are you going to find out about these queries - unless you limit the tempdb (for cases like this one in particular).

The other alternative is to grow the tempdb to an overly large size and then implement some monitoring that will capture the queries that cause these kinds of growths. Then you take the query back to the user that ran it and teach them about what they did wrong.

Use this article to help monitor growth of files.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thanks by the way, because now the current setting for the database file growth I restricted to 20gb, if I change it to unlimited, do I need to restart SQL service for this to take effect?

I know if I change tempdb file location, I need to restart the service so it can take effect, but not sure how about only changing the file growth part, do I also need to restart the service?

Thanks

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

Nope, no need to restart when changing that setting.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

sqlfriend

SSC Guru

Points: 52794

March 7, 2012 at 3:26 pm

#1456297

Thanks!

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

sqlfriends (3/7/2012)
Thanks by the way, because now the current setting for the database file growth I restricted to 20gb, if I change it to unlimited, do I need to restart SQL service for this to take effect?
I know if I change tempdb file location, I need to restart the service so it can take effect, but not sure how about only changing the file growth part, do I also need to restart the service?
Thanks

I think this is a very bad idea especially in light of the fact that you already know that you have something causing rampant growth in TempDB. Chances are, the query involved has a bad many-to-many join in it (think same result as cross-join) and it won't matter how big you make TempDB. It will consume whatever you give it and maybe still fail.

They need to reevaluate the code they've written and fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)