Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

Estimate the Tempdb size

We are moving our enviornment to a new SAN. Is there any best way to calulate the estimated size for allocating tempdb size?
0
venk_r
Asked:
venk_r
  • 4
  • 4
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
 
venk_rAuthor Commented:
Thanks for the reply.Very useful. Can you also tell me how I can montior tempdb usage?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
venk_rAuthor Commented:
thanks
0
 
venk_rAuthor Commented:
how do I calculate the size in(mb) based on the usedextents which is output of dbcc showfilestats?
0
 
venk_rAuthor Commented:
And also is there any way I can just get the tempdb log using dbcc sqlperf?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
(usedextents*64)/1024 = size in mb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now