Solved

TempDb

Posted on 1998-09-16
6
942 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now