sp_helpdb and sp_spaceused values differ, why? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sp_helpdb and sp_spaceused values differ, why?

HI Guys, There is one doubt in an analysis. I am getting differnet values of database size. When I use sp_helpdb, I get the databases sizes,
but when I use [ sp_msforeachtable ‘sp_spaceused "?"’ ]
in QA, I get the size is different.. In the sp_spaceused, I can clearly analyse the data, index, and unused with number of records. Why this differences of database size if I used these two QA commands? Thanks in Advance
-Johnson

hi,
sp_msforeachtable is undocumented sp,please run and check the result : sp_spaceused @updateusage = ‘TRUE’ — your current database and sp_helpdb ‘yourdatabase’ Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Hi,<br />and to compare with those values run<br /><br />dbo.sp_MSforeachdb ‘sp_spaceused'<br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
I tried the steps and analysed it, this gives me some more confusions. The result which i got in steps: 1)
sp_spaceused @updateusage = ‘TRUE’
2)
sp_helpdb ‘mydatabasename’ PRODUCTION_Data 1H:MSSQLDATAPRODUCTIONdata.mdf PRIMARY118349440 KBUnlimited10%data only
PRODUCTION_log 2F:LOGSPRODUCTION_log.LDF NULL 757248 KBUnlimited10%log only
PRODUCTIONind 3I:indexesPRODUCTIONINDEX_Data.NDF PRODUCTIONindex12170624 KBUnlimited10%data only
Total : 131277312 KB ( Approx : 128200.5 MB or 125.199 GB)
3)
But when I use
dbo.sp_MSforeachdb ‘sp_spaceused’ reserved data index_size unused
—————— —————— —————— ——————
101781528 KB 58632128 KB 42793424 KB 355976 KB
The "Reserved" is the sum of "data", "index_size" and "unused". Which is only 99393.33594 MB or 97.06 GB. 4)
My HDD utilization is , which matches with the [ sp_helpdb ‘mydatabasename’ ]
———————
PRODUCTION_Data – 112 GB
PRODUCTION_log – 672 MB
PRODUCTIONind – 11.6 GB Can you clarify this issue.. Thanks
Hi, can you run this after dbcc updateusage and sp_updatestat , and add log (.ldf) and index file size i(.ndf) in your 97.xx gb Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

I didn’t use "sp_updatestat" before trying all the steps..only used [p_spaceused @updateusage = ‘TRUE’] Let me try that too.. and revert back to you.. Thanks.
-Johnson
HI Hemantgiri, I have done including the sp_updatestats too. I can see the difference in the size when I applied this command:
dbo.sp_MSforeachdb ‘sp_spaceused’ reserved data index_size unused
—————— —————— —————— ——————
101781528 KB 58632128 KB 42793424 KB 355976 KB
The previous data was reserved data index_size unused
—————— —————— —————— ——————
101828112 KB 58647184 KB 42812904 KB 368024 KB
there is some changes in data… but still it not match with "sp_helpdb" data.
Is this coz sp_helpdb includes, object spaces ?? -Johnson

hi,<br />now run both statements given <br /><br />sp_helpdb ‘mydb'<br /><br />and <br /><br />ms_foreachdb ‘sp_spaceused'<br /><br />gives me the same result.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
Hi,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by johnson_ef</i><br /><br />HI Hemantgiri,<br /><br />I have done including the sp_updatestats too. I can see the difference in the size when I applied this command:<br />dbo.sp_MSforeachdb ‘sp_spaceused'<br /><br />reserved data index_size unused <br />—————— —————— —————— —————— <br />101781528 KB 58632128 KB 42793424 KB 355976 KB<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />The previous data was<br /><br />reserved data index_size unused <br />—————— —————— —————— —————— <br />101828112 KB 58647184 KB 42812904 KB 368024 KB<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">there is some changes in data… but still it not match with "sp_helpdb" data.<br />Is this coz sp_helpdb includes, object spaces ??<br /><br />-Johnson<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />my bad ,i think you are interpreting wron , please check the size value return by <b>sp_msforeachdb ‘sp_spaceused'</b> of very first row with <b>sp_helpdb ‘yourdb'</b><br /><b><br />please check the value containing row header ‘database_name’,’database_size’,’unallocated space'</b><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
Hi,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by johnson_ef</i><br /><br />HI Hemantgiri,<br /><br />I have done including the sp_updatestats too. I can see the difference in the size when I applied this command:<br />dbo.sp_MSforeachdb ‘sp_spaceused'<br /><br />reserved data index_size unused <br />—————— —————— —————— —————— <br />101781528 KB 58632128 KB 42793424 KB 355976 KB<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />The previous data was<br /><br />reserved data index_size unused <br />—————— —————— —————— —————— <br />101828112 KB 58647184 KB 42812904 KB 368024 KB<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">there is some changes in data… but still it not match with "sp_helpdb" data.<br />Is this coz sp_helpdb includes, object spaces ??<br /><br />-Johnson<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />my bad ,i think you are interpreting wron , please check the size value return by <b>sp_msforeachdb ‘sp_spaceused'</b> of very first row with <b>sp_helpdb ‘yourdb'</b><br /><b><br />please check the value containing row header ‘database_name’,’database_size’,’unallocated space'</b><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
hi Johnson ,
have you look at those value and compare it !
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Sorry ghemant, I cam office for now… let me try all… will get back to you.. Thanks
-Johnson
hi Hemantgiri, This is the our put which I got when I apply both these commands,
In both of these command db_size and database size shows same (128200.50 MB) I assume that, 102050232 KB (99658.43 MB )which shows in the "Reserved" is the actuall data size excluding Objects like tables, SPs etc. If its the same, then I am clear about the info… Over to you sir..
************************** sp_helpdb ‘mydb’
Output: name db_size owner dbid created status compatibility_level
———————– ————- ———————- —— ———– —————————————————————————————————————————————————————————————————————————————————————- ——————-
PRODUCTION 128200.50 MB TNMAINadminit 7 Sep 4 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics 80
name fileid filename size maxsize growth usage
————————– —— ——————————————————- ——————————————————————————————————————————– —————— —————— —————— ———
PRODUCTION_Data 1 H:MSSQLDATAPRODUCTIONdata.mdf 118349440 KB Unlimited 10% data only
PRODUCTION_log 2 F:LOGSPRODUCTION_log.LDF 757248 KB Unlimited 10% log only
PRODUCTIONind 3 I:indexesPRODUCTIONINDEX_Data.NDF 12170624 KB Unlimited 10% data only
==================== sp_MSforeachdb ‘sp_spaceused’
Output: database_name database_size unallocated space
——————- —————– ——————
PRODUCTION 128200.50 MB 27802.57 MB
reserved data index_size unused
—————— —————— —————— ——————
102050232 KB 58747792 KB 42913560 KB 388880 KB **************************
-Johnson
Hi Johnson,
yes its your actual db size .
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Thanks Hemantgiri, Thanks a lot!!! -Johnson

Most welcome
Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

]]>