Solved

Estimate the Tempdb size

Posted on 2009-06-27
8
676 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
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.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

16 Experts available now in Live!

Get 1:1 Help Now