temp db size

Question

temp db size

George Nak

SSC Enthusiast

Points: 187
More actions
September 2, 2009 at 4:12 pm

#204192

Dear all
i am a newbie in the sql server and i have been delegated the sql admin role in company as the old administrator has quit.
we have an sql cluster 2000 and i want to change the tempdb size to its original settings
autogrow by 10% and unlimited file growth.
It appears that the previous admin has "alter" these settings and although i turn the settings after a restart of the sql server service the percent setting does not apply instead it is checked as grow by 1 mb.
How can i permanently change that to 10% ?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

George Nak (9/2/2009)
we have an sql cluster 2000 and i want to change the tempdb size to its original settings

For a lot of reasons, that's probably the worst thing you could do. Fragmentation on reboot, delays during growth, unknown amount of growth, etc, etc, etc.

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

George Nak SSC Enthusiast Points: 187 More actions · Answer 2

Thank you Jeff for your reply.

I assume that you mean it is not a very good practice to change the auto grow setting to percent.

So shall i leave the tempdb grow by MBs instead of percent ?

Thank you in advance.

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

What were the settings set to, before you changed them. unless you have good reasons to change them I would leave them as they are.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

George Nak SSC Enthusiast Points: 187 More actions · Answer 4

The size of the db was limited to 8 GB with the log file to increase by 512 MB and the data file for 2048 MBs

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

George Nak (9/3/2009)
The size of the db was limited to 8 GB with the log file to increase by 512 MB and the data file for 2048 MBs

Hi George,

To also answer your other question as well as this one, I wouldn't LIMIT TempDB to 8GB... I'd START it at 8GB as the initial size and I'd monitor it after that. The growth of TempDB should never take the system by surprise... it should be a planned exercise. Just to keep runaway queries from killing the system, I'd probably LIMIT TempDB to 50GB but that would be just to give me the time to find the run away query causing it's growth. Of course, you could setup a permanent profiler run to monitor for such growth but you'd need to include other things to figure it all out because the growth events don't capture the TEXTDATA that caused the growth.

I do believe the growth settings you have above are just a little large if growth does catch you by surprise but I can see the plan that someone else had... that plan would be to never let TempDB growth take you by surprise. 😉

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

George Nak SSC Enthusiast Points: 187 More actions · Answer 6

Hi Jeff

Actually the size has never grown above 4 Gbs for this db.

Also i have found the script which was used to modify its size.

i am attaching it here.

The shared disk (it is a cluster ) has the size of 100 GBs.

I will try to monitor more and learn more things about tempdb.

The real problem is that we have a database ,17 Gb, that every morning pulls data from an AS400 system and inserts them on the database.

Many times the job hungs and all the locks are in status sleeping giving no errors or warning messages.

So from the little things that i know for sql 2000 i am trying to see how modifying the tempdb size could help the situation.

If you want more details about the systems let me know and i can attach you every information available.

Thank you all for your time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

George Nak (9/3/2009)
Actually the size has never grown above 4 Gbs for this db.

If that's the case, make the starting size 4 GB, the growth around 100MB and change the max to unlimited.

What's the size and growth of the log?

The real problem is that we have a database ,17 Gb, that every morning pulls data from an AS400 system and inserts them on the database.
Many times the job hungs and all the locks are in status sleeping giving no errors or warning messages.

Sleeping means that the connection has finished executing queries and is waiting for another command to run. Sleeping connections are seldom a problem. Suspended means waiting for a resource so that it can continue processing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

George Nak SSC Enthusiast Points: 187 More actions · Answer 8

The size of the log file has never exceeded the 500 MBs , for tempdb,.

Now for the job itself.

The thing is that when the job is running and we come to the "sleeping" status on the processes window

a We terminate the job

b Do a restore of the database as the company that created the application and the database cannot guarantee how many records have been updated or deleted

c Re run the job and then it is being executed with success.

The job consists of dts packages.

Thanks

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

George Nak (9/3/2009)
The size of the log file has never exceeded the 500 MBs , for tempdb,.
Now for the job itself.
The thing is that when the job is running and we come to the "sleeping" status on the processes window
a We terminate the job
b Do a restore of the database as the company that created the application and the database cannot guarantee how many records have been updated or deleted
c Re run the job and then it is being executed with success.
The job consists of dts packages.
Thanks

That probably has nothing to do with TempDB at all. It may have to do with an uncommitted transaction or a memory leak, but probably not TempDB.

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

George Nak SSC Enthusiast Points: 187 More actions · Answer 10

Thank you Jeff

Ok i got it and how do i check for memory leaks ?

The servers have 16 GB of RAM and run on 2 4 core CPUs.

The AWE is enabled and the instance has min mem set to 1024 and max mem to 3072 MBs.

You are very kind and helpful all of you.

txtPost_CommentEmoticon(':-)');

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

Ugh! Sorry George... I'm not so hot at finding the cause of a memory leak. If you monitor the memory in the Windows side of the server, you can determine if one exists but I'm not sure how to isolate it in GUI code. In SQL Server, you can check for the use of sp_OA* and make sure that all such things get closed. I suppose the same thing goes for CLR's... any resources used do have to be closed before they exit.

First, like I said... I'd monitor the Windows Server itself and see what memory does.

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