Solved

TempDb

Posted on 1998-09-16
6
952 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:rvindust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:formula
ID: 1090099
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.

0
 

Author Comment

by:rvindust
ID: 1090100
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?
0
 
LVL 2

Expert Comment

by:formula
ID: 1090101
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!
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rvindust
ID: 1090102
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.
0
 

Accepted Solution

by:
manish77 earned 20 total points
ID: 1090103
rvindust,
I have a 1.5 GB database. My users are 25 concurrent. Before I came on board the SQL server use to frequently come down with no space on temp db stsatement. I increased the size of temp db from 50 to 250 MB. Memory is cheap so ask for a additional hard disk. Even now the temp db some time fills up if some body runs a runaway query. In that scenario go to enterprise manager kill the connections and Dump the Temp Db with with No log.
You mentioned that you have 25 people working on your SQL server. They will very easily fill up the 60 MB you have curently since every connection is assigned some space on temp db which increases as an when activity on your DB goes up especially the order by query or any temp table creations.

My advice to You is increase your Temp Database size to atleast 150 MB and increase the size of your Log Segment till you stop getting this error. Even though your Data Size is not much you are seeing lot of trasactional activity on your Database.
Hope this help.
Manish77
0
 

Author Comment

by:rvindust
ID: 1090104
I would like to give the points to both formula and manish77, but Manish77 was very informative
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question