Solved

Estimate the Tempdb size

Posted on 2009-06-27
8
665 Views
Last Modified: 2012-05-07
We are moving our enviornment to a new SAN. Is there any best way to calulate the estimated size for allocating tempdb size?
0
Comment
Question by:venk_r
  • 4
  • 4
8 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24727653
<<We are moving our enviornment to a new SAN. Is there any best way to calulate the estimated size for allocating tempdb size?>>
If you already have a working TEMPDB: multiply the *highest* used space of tempdb PRIMARY filegroup t by 5 then divide that number by the total number of cores on the server to obtain the size each file .   To get that highest value of tempdb used space, you need to monitor the system during at least 2 to 3 days because the value changes all the time.

Example: if the highest value of used tempdb space in a day is 2Gb on a 4 core server then you need about 10Gb of total space  If you have 4 cores then create 4 files of 2.5Gb each and 1 file of 64K.  The 4 files should be configured to NONAUTOGROW and the last file to AUTOGROW (as a safety net in case of the first 4 is filled).

Hope this helps..
0
 
LVL 8

Author Comment

by:venk_r
ID: 24728841
Thanks for the reply.Very useful. Can you also tell me how I can montior tempdb usage?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24729006
<<Can you also tell me how I can montior tempdb usage?>>
You can use  dbcc showfilestats for data files and dbcc sqlperf(logspace)  for log space...
Ex:
use tempdb
go
dbcc showfilestats
then you have total used space with the following formula: (usedextents*64)/1024

run this every minute and take the highest value in the day.
 
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 250 total points
ID: 24729016
What yo can do is program a job that would run every 5 minutes that inserts the content of both dbcc showfilestats  and dbcc sqlperf(logspace)  into 2 different tables.  Once done, simply estimate the highest value of both tempdb data file and tempdb log file.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Author Closing Comment

by:venk_r
ID: 31597543
thanks
0
 
LVL 8

Author Comment

by:venk_r
ID: 24738109
how do I calculate the size in(mb) based on the usedextents which is output of dbcc showfilestats?
0
 
LVL 8

Author Comment

by:venk_r
ID: 24738119
And also is there any way I can just get the tempdb log using dbcc sqlperf?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24738233
(usedextents*64)/1024 = size in mb
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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.
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…
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.

743 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