none
dbcc updateusage Is it ok to run online in the middle of the day on a busy website?

    Question

  • Does this lock tables while running. 

    It only takes about 50 secs on stage but not sure if it is locking tables and generally making the site unavailable.  I would prefer to run it while at work rather than schedule it for night.  Thanks, Leon
    Wednesday, June 03, 2009 2:30 PM

Answers

  • What's the intended goal for running this?  Usage statistics are updated periodically by SQL Server as it runs, so unless you are trying to fix a problem reported by DBCC CHECKDB, and or get an accurate size for a specific purpose, I wouldn't just adhoc run UPDATEUSAGE, there isn't much reason to.  It is safe to schedule it to run afterhours, it is just recalculating the space used by the database and its objects, as reported by sp_spaceused.

    The BOL Entry for it lists the following best practice information:

  • Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
  • Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
  • 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.
  • Unless you just upgraded, or you fit the last scenario, or CHECKDB is outputting that you need to run UPDATEUSAGE, you probably don't need to.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/jmkehayias
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
Thursday, June 04, 2009 12:31 AM
  • Leon,

    DBCC UPDATEUSAGE takes Shared lock on the table and data modifications to the tables will be stalled (blocked). Does stage and production has same database size and hardware? It also depends on how busy the server is at the time of running this command. So use your best estimate and if you can schedule it.

    | Sankar Reddy | http://sankarreddy.spaces.live.com/Locations of visitors to this page |
    Wednesday, June 03, 2009 2:42 PM
  • All replies

    • Leon,

      DBCC UPDATEUSAGE takes Shared lock on the table and data modifications to the tables will be stalled (blocked). Does stage and production has same database size and hardware? It also depends on how busy the server is at the time of running this command. So use your best estimate and if you can schedule it.

      | Sankar Reddy | http://sankarreddy.spaces.live.com/Locations of visitors to this page |
      Wednesday, June 03, 2009 2:42 PM
    • What's the intended goal for running this?  Usage statistics are updated periodically by SQL Server as it runs, so unless you are trying to fix a problem reported by DBCC CHECKDB, and or get an accurate size for a specific purpose, I wouldn't just adhoc run UPDATEUSAGE, there isn't much reason to.  It is safe to schedule it to run afterhours, it is just recalculating the space used by the database and its objects, as reported by sp_spaceused.

      The BOL Entry for it lists the following best practice information:

    • Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
    • Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
    • 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.
    • Unless you just upgraded, or you fit the last scenario, or CHECKDB is outputting that you need to run UPDATEUSAGE, you probably don't need to.


      Jonathan Kehayias
      http://sqlblog.com/blogs/jonathan_kehayias/
      http://www.twitter.com/jmkehayias
      http://www.sqlclr.net/
      Please click the Mark as Answer button if a post solves your problem!
    Thursday, June 04, 2009 12:31 AM