Solved

TempDb

Posted on 1998-09-16
6
935 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rvindust
Comment Utility
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
Comment Utility
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
Comment Utility
I would like to give the points to both formula and manish77, but Manish77 was very informative
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

763 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

15 Experts available now in Live!

Get 1:1 Help Now