Calculatin?g Tempdb sizes giving different results

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Calculatin?g Tempdb sizes giving different results

Author

Topic

Martyn123
Starting Member

29 Posts

Posted - 2011-08-04 : 06:22:13

Hi,

On one of our production server when I checked the tempdb size using following command:

SELECT
a.FILEID,
CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,5)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
a.name as [DATABASENAME],
a.FILENAME as [FILENAME]
FROM dbo.sysfiles a

I got the output as follows:

FileId File Size(MB) Space used (MB) Free space (MB)
1 1869.06 1830.25 38.81
2 5.06 3.16 1.90

but when I am executing the stored procedure on tempdb : exec sp_spaceused

I am getting the following output:
databasename databasesize unallocated space
tempdb 1874 MB 1868 MB

and when I am right clicking on the tempdb --> properties--> clicking General : on the right panel I am getting as size=1874 MB and space available=1868 MB .

I don't understand how unallocated space or space available is 1868 MB when above query is giving spaceused as 1830 MB and free space as 38.81 MB

So please help me in understanding which one is correct either result of the dbo.sysfiles query or the result of the sp_spaceused.

If dbo.sysfiles query is correct then why checking the properties of tempdb by above method or sp_spaceused is giving so different result.

Please help me out in understanding this behaviour and if anybody has any new dmvs or any stored procedures or any queries that gives me the exact result please share it with me.

Thanks
Martyn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-04 : 07:42:45

1 and 2 are the master databases.
Make sure your current database is TEMPDB before running your code.

N 56°04'39.26"
E 12°55'05.63"

Martyn123
Starting Member

29 Posts

Posted - 2011-08-04 : 08:00:25

Yes I am running the above two queries on tempdb only.

muratos
Starting Member

22 Posts

Posted - 2011-08-05 : 04:46:59

I have run the same queries on my system temp. I had very close results like 1996 MB to 1981 MB for unused (unallocated) spaces. The difference can be something because of reserved space internally. I just don't know.

I suspect that you just mistakenly read 38MB spaceused as freespace column. Can you run it again?

muratos
Starting Member

22 Posts

Posted - 2011-08-05 : 05:06:33

Here is the info about sp_spaceused

http://msdn.microsoft.com/en-us/library/ms188776.aspx

As far as I understand, its results are a bit misleading. For example, unallocated space does not consider log files but database_size shows data + logs together. So, I believe the sysfiles query or right clicking on exact object would give the exact result.

Someone can correct me if I am not right please?

muratos
Starting Member

22 Posts

Posted - 2011-08-05 : 08:31:03

DBCC UPDATEUSAGE

This could be also related. See the details below please.

http://msdn.microsoft.com/en-us/library/ms188414.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources