Tempdb size difference with sp_database/sp_helpdb

Question

Tempdb size difference with sp_database/sp_helpdb

JS50366

SSC-Addicted

Points: 473
More actions
September 22, 2011 at 9:16 am

#257891

New to the forum.
I found out the tempdb size looks different when I use
sp_database/sp_helpdb.
tempdb8704NULL (sp_database)
tempdb 15.50 MBsa2Sep 16 2011Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics100 (sp_helpdb).
Would someone help to explain the difference? The sp_database is not accurate.
I appreciate your input.
Thanks,

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

sp_database isn't a built in procedure, it must be one that your group added;

i'm guessing that 8704 is the number of pages but i'm not sure without seeing the procedure itself. the # doesn't seem to match what i think a 15Mb database should be, though.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LAW1143 SSCrazy Points: 2744 More actions · Answer 2

the quickest dirtiest way is to look at the size of your data file(s). SSMS doesnt reflect increases in tempdb size. its available in the DMVs, but just to see how big it is, not necessary.

if you need more, I can provide some of the queries ive been using for reasearching some annoying tempdb problems in the last week or two.

JS50366 SSC-Addicted Points: 473 More actions · Answer 3

Thanks for the replies and I will continue to research the tempdb problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I wouldn't classify this as a problem, more like an issue between two different reporting processes. Without understanding how sp_database reports space/size, we just aren't in a position to offer much help. If you code post the code for this procedure we could help you better.

JS50366 SSC-Addicted Points: 473 More actions · Answer 5

I used standard sp.

exec sp_databases

DATABASE_NAMEDATABASE_SIZEREMARKS

tempdb8704NULL

------------------------------------------

exec sp_helpdb

namedb_sizeownerdbidcreatedstatuscompatibility_level

tempdb 15.50 MBsa2Sep 16 2011Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics100

---------------------------------

exec sp_spaceused (use tempdb)

database_namedatabase_sizeunallocated space

tempdb15.50 MB12.67 MB

reserveddataindex_sizeunused

1616 KB728 KB720 KB168 KB

-------------

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB

FROM sys.master_files

WHERE DB_NAME(database_id) = 'tempdb'

GO

DatabaseNameLogical_NamePhysical_NameSizeKB

tempdbtempdeve:\Program Files\**\tempdb.mdf8192

tempdbtemploge:\Program Files\**\templog.ldf512

Finding:

database size from sp_databases = tempdb.mdf+tempdb.ldf

database size from sp_helpdb = sp_spaceused (use tempdb)

Just cannot figure out where the difference goes.

Thanks for all the replies.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Ran sp_databases and sp_helpdb on one of our servers here, didn't get a discrepency after converting to the same unit of measure. I am at a loss for the difference you are seeing.

JS50366 SSC-Addicted Points: 473 More actions · Answer 7

I feel strange because for other databases seem ok. But for tempdb there is difference. I just gave out one example with big difference from one of our servers. Thanks for the reply. Would you be able to check multiple servers with different versions (05,08)?