Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anbu_sql
Starting Member
4 Posts |
 Posted - 2007-08-08 : 06:40:57
|
| 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---------- 1024kbThe 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 regardThanks in Advance- Anbu |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-08 : 14:36:05
|
| You can add @updateusage=true option in sp_spaceused. |
 |
|
|
anbu_sql
Starting Member
4 Posts |
Posted - 2007-08-09 : 00:23:07
|
| Yes i have tried that also...Will "@updateusage=true" option show that exact size? if i released few DB sessions or my batch jobs are done the space usage should be reduced right. If its so, can i see the reduced file size like 168GB to 95GB. I guess the file size will be reduced when we shrink the database. I doubt like internally it releases some space (may be sleeping sessions space) and using it current transactions.I want to capture that floating size varition.Thanks,anbu |
|
|
|
anbu_sql
Starting Member
4 Posts |
Posted - 2007-08-09 : 00:27:35
|
| Internally space is floating but SP_SPACEUSED and MDF and LDF file size showing the expanded size only.Thanks,anbu |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-09 : 23:10:27
|
| Don't shrink it if there is enough disk space. |
|
|
|
|
|
|
Find TempDB Actual space occupied or used