inconsistency between sp_spaceused and sp_helpfile

• Igor Polishchuk

  SSC Veteran

  Points: 246

  More actions

  March 9, 2009 at 9:19 am

  #206760

  Bellow is the result of sp_helpfile and sp_spaceused that show inconsistent data. sp_spaceused shows that database size is 48GB when sp_helpfile shows that datafiles size is 20GB. Any idea ?

  Thanks.

  exec sp_helpfile;

  name filename size

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

  servicedata_Data1 G:\mssql_data\servicedata\servicedata_1.mdf 15741184 KB

  servicedata_Log H:\mssql_log\servicedata\servicedata_log.ldf 28774400 KB

  servicedata_Data2 G:\mssql_data\servicedata\servicedata_2.mdf 2073152 KB

  servicedata_Data3 G:\mssql_data\servicedata\servicedata_Data3.ndf 3072000 KB

  EXEC sp_spaceused @updateusage = N'TRUE';

  database_name database_size unallocated space

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

  servicedata 48496.81 MB 2779.98 MB

  reserved data index_size unused

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

  18039640 KB 12121288 KB 5857368 KB 60984 KB

• David Benoit

  SSC-Dedicated

  Points: 34562

  More actions

  March 9, 2009 at 9:40 am

  #956135

  sp_spaceused includes both the data and log files in the size represented.

  David

  @SQLTentmaker

  “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

• SQL_EXPAT

  SSCrazy

  Points: 2708

  More actions

  March 9, 2009 at 9:47 am

  #956147

  in addition if you still see inconstencies then run @updateusage=TRUE to update space figures.

  thanks

  SQL_EXPAT