Tempdb growing huge

Question

Tempdb growing huge

shohan_db

SSC Rookie

Points: 44
More actions
July 20, 2010 at 2:10 am

#94988

i am working with sql server 2000 & few days ago i found my tempdb database become so large. it was nearly 200MB but within 12 hours in became 15 GB!
>>My tempdb’s current properties are
a. Recovery model : Simple
b. Size: 14016 MB, Space available: 14015 MB
c. Automatically grow file & Unrestricted file growth
d. File growth rate: 10%
it should mentioned that no of select/insert/update or delete query not increase in my database within that period so it seems quite unusual to me…
Whatever I found some solution from net and one of them I chose following solution:
-------------------------------------------
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Deisred target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file
Stop and start SQL SERVER....
----------------------------------------------------
My questions are:
01. if I manually resize tempdb.mdf and tempdb.ldf file
Will it lose any data from tempdb?
02. Will it affect any on my user database?
I am very confused about that issue, if anyone has better solution please advice me.
Thanking you
Mainur Rahman Shohan

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Here is the thing, if you do not find out why the TempDb is growing, then what ever you do now, you will end up with the same situation again. The TempDB can grow when large due to one bad query (select) that was run or it could be a bulk update or bulk insert. If that is the case you have to make sure that the transaction is complete before you do anything.

You have your recovery model as simple. Keep in mind that you are restricting yourself with no DR strategy for this DB.

Like the name implies, TempDB is just Temporary. You will not loose data in your main DB. (Make sure what ever transaction caused this growth is complete)

-Roy

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I agree with Roy. You have to find out what the cause of the growth is before you do anything else.

As a side bar, a 15GB TempDB may not be all that outlandish depending on what you're doing and how big tables and indexes are.

--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)

David Sorauer SSCrazy Points: 2874 More actions · Answer 3

Is there potentially any database maintenance causing this (using the tempdb for sorting etc)?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

As Roy and Jeff have said, find what is causing the growth to occur.

Here is an article discussing how to do that.

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