Find TempDB Actual space occupied or used

Question

Find TempDB Actual space occupied or used

anbu-474857

SSC-Addicted

Points: 408
More actions
August 8, 2007 at 4:59 am

#68879

How to find actual space used (exact size) in TempDB (Sql Server 2000).
I have used SP_SPACEUSED. SP_SPACEUSED returns the DB size in MB but have question like whether returns
actual space used. becuase shows same size in my case.

I am monitoring the DB size for past 1 month. Initially the size was 7078 MB and the maximum size of mdf
and ldf files have been set to MDF-->166468608 KB LDF-->5633024 KB. we have batch jobs (stored procedures)
running every weekend which will increase the TemDB size and also some reporting tools used in the
weekdays. Initially two weeks the size was increasing significantly but once it reached the size of
MDF-->166466880 KB (difference b/w maximum size and actual size is 1728 KB) it is not growing and not even
throwing error also.
here is the data.
Current size                    Initial max size           Difference        File                      Growth
=========================================================================================
166466880 KB                  166468608 KB            1728 KB         tempdev               10%
5632000 KB                     5633024   KB             1024 KB         templog                1024kb
The current file size persist for last 20 days. still the week end job are running and reports are using
the database but it doesn't throw any error.
Is there any internal space available in mdf and ldf file apart from the size shown?
This is what i am wondering how to find actual space used and available in TempDB in Sql server 2000.
I suppose to get back the answer soon. Please help me in this regard
Thanks in Advance
- Anbu

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

use tempdb
-- Show Size, Space Used, Unused Space, and Name of all database files
select
        [FileSizeMB] =
                convert(numeric(10,2),round(a.size/128.,2)),
        [UsedSpaceMB] =
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
        [UnusedSpaceMB] =
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
        [DBFileName] = a.name
from
 sysfiles a

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 2

Um... just making sure... you're saying that the current size of TempDB is over 166 GIGA BYTES ???

I'm thinking there's a couple of problems with the code that makes it grow to such an enormous size... are you using lot's of cursors, temp tables, or XML?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

philcart SSC-Forever Points: 47797 More actions · Answer 3

Jeff I thought you'd do the math a little better. It's ONLY 158GB

What are you using to produce the numbers you've given us? Maybe your calculation is off by a bit more than Jeff's

--------------------
Colt 45 - the original point and click interface

anbu-474857 SSC-Addicted Points: 408 More actions · Answer 4

Hi Jiff and Phill,

Yes you are right..we use lots of cursor and #temp tables in our weekend jobs because basically ours is dataware house related project we suppose dump data from host system to SQL server.So Increasing size is not a problem. but i want to capture the size variation like if usage is more it grows till 160GB (not in a day...week by week) but not reduced when all jobs done and sessions are killed.

I hope the file size will be reduced only when we shrink the database. I guess interanlly it releases some space (may be inactivie sessions) and allocates for new transactions. so the size is floating internally. because i have set maximum size as 168GB, the difference b/w current and actual is just 1.7MB with this size all jobs and reports are working without thorwing error.

I just want to capture the internal space variation.

I am yet to try Michel Valentine script given here. I wil try and let you guys know.

Thank you Michel Valentine.

If you guys faced same kind of issue. please let me know.

Thanks,

anbu

anbu-474857 SSC-Addicted Points: 408 More actions · Answer 5

Yes...The script posted by Michel has resulted the proper output i could capture the exact used space. The used space floats up and down when insert some data into #temp table and drop the table.

Thank you so much Michel!!..

-Anbu

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 6

I noticed that the growth rate is set to 10%. At the current size, that means that then next growth will be approximately 16 gig... that will take quite a while to allocate... you sure you don't want to set it to a fixed, somewhat smaller size?

And, are you sure the approximate 166 gig that you're using is worth it? What I mean by that is it's grown to that size because of the cursors and the way you are using temp tables... if you're daily/weekly procs are taking a long time to run, perhaps its worth revisiting how the code has been written.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jenny-443556 SSC Journeyman Points: 87 More actions · Answer 7

you can go to Enterprise Manger, right click on your tempdb, from shortcut menu, click view - > taskpad, you will find out space allocated for your database files.