Link to home
Start Free TrialLog in
Avatar of rvindust
rvindust

asked on

TempDb

I am having a problem with the tempdb. It fills up (60 Meg) in about 6 hours with approx. 25 people using the SQL server. Is this normal? What do I have to do to correct this? I have been shutting the server off and restarting, this seems to take care of the problem. Is there a way around this?
Avatar of formula
formula

If tempdb is filling up, check these things:

1) Could be the logsegment, therefore make sure tempdb is set
  to "truncate log on checkpoint".
2) Consider making tempdb larger, if users are writing to tempdb
that much.  The logsegment may still fill up if one user transaction is larger than the size of the log, so increase log space to account for this if that's the problem.
3) Increasing memory allocated to SQL Server can help too.

Avatar of rvindust

ASKER

What does the tempdb hold that requires so much memory, and why can't I truncate it when there are still users on the system?
All users share the same tempdb for worktables and temporary tables, so depending upon what's going on, tempdb could be a bottle neck.  Your description of the problem indicates that tempdb is filling up, either on the data segment or the logsegment or both, because when you toggle the server on and off, tempdb is reset and works for awhile.  You can truncate it when users are on the system, but if there's an active transaction that's big enough to use all the current space, it won't help.  Tempdb should be about sized about 25% of your largest database as a start, and if you create alot of temporary tables or your queries have many "order by" or "distinct" clauses and you have many users, it may need to be larger. Example: My app has a 4GB db with about 20 concurrent users and my tempdb is 800 mb (db set to truncate log on checkpoint) and it works fine.  Disk space is cheap, so try it and see. Good Luck!
My Database is about 60 meg.  I have increased my tempdb to 65 meg (This seems huge). Does increasing the logsegment help This problem out.
ASKER CERTIFIED SOLUTION
Avatar of manish77
manish77

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would like to give the points to both formula and manish77, but Manish77 was very informative