December 29, 2006 at 9:33 am
Can someone tell me why the results for size between the select statement for tempdb is different than the results from sp_spaceused? The datafilesize + logfilesize from the select should equal the database_size from sp_spaceused. I have a routine that logs space info about all DBs but it doesn't see to be working for tempdb. The info I get back doesn't match sp_spaceused or the file size (which does match sp_spaceused). I can post the full code later and see if there is any way to improve the process and share with others. I have been changing it for SQL 2005.
Thanks
Select Master.sys.databases.database_id,
Master
.sys.databases.Name,
Round(convert(dec(12,2),datafile.size) * 8 / 1024, 2) datafilesizeMB,
Round(convert(dec(12,2),logfile.size) * 8 / 1024, 2) logfilesizeMB
From
Master.sys.databases
Left
Join Master.sys.master_files datafile on sys.databases.database_id = datafile.database_id and datafile.type in (0, 4) -- data file
Left
Join Master.sys.master_files logfile on sys.databases.database_id = logfile.database_id and logfile.type = 1 -- log file
exec
sp_spaceused
December 29, 2006 at 11:51 am
Don't you have to agregate across all types ?
If your database happens to have more than one Data/log file your query does *not* work
Also Tempdb has some other data structures (unique to it, like the version store) in 2005 not covered by sp_spaceused
Cheers.
* Noel
December 29, 2006 at 12:40 pm
Types 2 and 3 are reserved for future use but you are right it should agregate.
You are correct that the query shown will not work for DBs that have the data or log split into more than one file each.
I should have included that i my case the select statement returned only one row for tempdb with a total size(data+log) of 207.44 MB where sp_spaceused returned a total size of 4172.00 MB. sp_spaceused returned the correct results. I have confirmed that there are only two rows in sys.master_files for tempdb.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply