syntax check to execute DBCC UPDATEUSAGE for all user DBs

• lan2022nguyen

  SSC-Addicted

  Points: 459

  More actions

  October 12, 2023 at 12:22 pm

  #4306613

  please confirm the following t-sql for correctness:

  EXEC sp_MSforeachdb 'USE ? IF "?" NOT IN ("master", "model", "msdb", "tempdb")
  DBCC  UPDATEUSAGE(?) '

• frederico_fonseca

  SSCoach

  Points: 16167

  More actions

  October 12, 2023 at 1:45 pm

  #4306678

  this is the type of sql you should confirm yourself in a dev box - if it is wrong you will see the errors.

  as for executing it blindly as you have above - as per the documentation https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-ver16

   

  We recommend the following:

  • Don't run DBCC UPDATEUSAGE routinely, as SQL Server maintains the metadata under most circumstances. DBCC UPDATEUSAGE should be run on an as-needed basis, for example, when you suspect incorrect values are being returned by sp_spaceused. DBCC UPDATEUSAGE can take some time to run on large tables or databases.
  • Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.