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
Solved

Estimate the Tempdb size

Posted on 2009-06-27
8
679 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

860 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