Sp_Spaceused Command Executing Too Long and Update Statistic Command

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  July 11, 2005 at 8:25 am

  #167460

  We have SQL Server 2000, SP3. I excuted the following commands on one of our Databases:

  1) backup log dbname with truncate_only

  2) dbcc shrinkfile (log, 10000)

  Then I tried to execute:

  sp_spaceused @updateusage=true

  To recalculate the space used and to obtain an updated report. The command (sp_spaceused) has been running for over 50 minutes. I think I am going to stop the command at 1 hour of execution time.

  Does anyone know why this command is taking so long? How may I speed it up? Also, (another related item) is there any way to manually update table statistics (with a command)?

  Thanks in advance for your assistance, Kevin

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  July 11, 2005 at 8:35 am

  #572938

  Check if there's a lock on the info you are trying to fetch.

  run exec sp_who2 and check the blkby column for any spid -

  then for each of those ids run :

  dbcc inputbuffer (spid)

  this will show you the last statement executed by that connection.

  Then if you want to kill that process :

  Kill spid

  Then your query should execute.