TempDB

Question

Post reply

TempDB

Krasavita

SSC-Insane

Points: 23138
More actions
September 24, 2008 at 10:41 am

#131385

Hello, I was wondering if you could help me. I get Message alert that tempdb is full, so as far as I understand I need to add more space for tempdb,bit I don't know if I am doing right:
My temp tempdev: is 8MB and templog: is 1MB. My largest db is 630MB. Can I change from 8MB to 500mb and from 1 to 258 (20%) than biggest db, is this correct?
Here is my setting for temp DB:
DBSize Unalocated size
tempdb 9.00 MB 7.22 MB reserve data index_size unused
800 KB 248 KB 384 KB 168 KB
tempdev
PRIMARY 8192 KB Unlimited 10% data only
templog
NULL 1024 KB Unlimited 10% log only
Also, do you usually do use unrestricted file grows or restricted, if restricted how do you run by? Can I changed settingis anytime of tempdb or I have to restore SQL Server?

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

A 500 Meg TempDB for an instance where the largest database is 630 Meg in size appears to be an overkill.

Set your TempDB to autoextend as needed with a limit of 200 Meg and monitor how it behaves for a week or two.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

usually you see this message when you have autogrow enabled, but when a transaction needs more space than the autogrow will allow, or you have autogrow turned off for that db.

If temp currently is 10 MB and the autogrow setting is 10%, it will grow 1MB to give you 11MB. If the transaction requires still more space than that, it will be rolled back saying the db is full.

If you manually specify a size for Temp db when the server is restarted it will be rebuilt at the size you specifed no matter if it's grown at all or not.

Have a gander at this link and see if it helps you at all...

http://www.extremeexperts.com/SQL/Articles/AutoGrowth.aspx

To help us help you read this[/url]For better help with performance problems please read this[/url]

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

Can I change it anytime, do I need to re strat the server? Thank you

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

yes it can be changed without restarting your SQL service.

Look up the Alter Database Command in Books Online.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

My autogrow is set to unrestricted grows by 10% and still don't understand why I am getting this message.Thank you

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

Did you review the link I posted? Some times things take more space than autogrow will allow. The db won't autogrow twice for the same transaction. It will be rolled back, however when it runs again, it will autogrow the second time and it might then have enough space to do whatever it needed to do.

That's why everyone is saying to set your tempdb to a certain space 20, 50, 100 MB and monitor it over the nextg week/month whatever and see what your usage is like.

Once you get to a certain point tempdb shouldn't really need to grow much because it will be able to handle the sorts and such that you need. 10MB probably isn't enough. when it auto grows 10% to 11MB that still may not be enough. It might not be enough until it autogrows to 15, 20 whatever, but when you server is restarted and if you didn't specify a size for tempdb, it will start back out again at whatever the default is and have to autogrow itself back to where it was. This leads to disk fragmentation and will ultimately slow down your performance.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

Thank you very much, I am new with this all DBA and don't understand a lot, so I will change from 10% to 20% and monitor for the week if I get an error message that my tempdb is full right? Do I change it data and log or just data?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 8

If you didn't change it from the default then your tempdb recovery model is set to simple. If this is the case then there really is no reason to have your tempdb log growing much at all.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Krasavita SSC-Insane Points: 23138 More actions · Answer 9

Yes, I din't change it, it's in simple, so leave log alone and add change data to 20% right?

Luke L SSC-Dedicated Points: 35547 More actions · Answer 10

Sure, that could be a good starting point. You will just need to keep an eye on it. Once it gets to a place where it doesn't seem to be growing any more it would be a good idea to set the size to perhaps 1 MB larger than it is and move autogrow back to 10%.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]