Solved

TempDb

Posted on 1998-09-16
6
949 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 85
SQL DATEADD 10 69
CDC and AOG on MS SQL 2012 13 23
TSQL convert date to string 4 34
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

791 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