Monday 2 April 2012

DBCC UPDATEUSAGE

 
SQL Server cannot guarantee accurate allocation information and row counts are to prevent database blocking.

If table space allocation and row count information were to be maintained accurately on every INSERT and DELETE, or when an index is dropped, or when a large bulk copy operation is performed, database concurrency could suffer as users in a high transaction environment wait for their transactions to complete as the space information is maintained in real time.

To ensure accurate space allocation reporting by sp_spaceused, DBCC UPDATEUSAGE can be executed against the database to scan all data pages and to update all space allocation information. This command can be run against a specific database table or against an entire database.

Syntax is mentioned bellow:

 

      DBCC UPDATEUSAGE ( database

         [, {table | view}  [,{index} ] ] )

            [WITH [ NO_INFOMSGS ] [ , ] [COUNT_ROWS ] ]

 

 Keys are:

 

 database    - 'database_name' or database_id or 0 (current db)

 

 NO_INFOMSGS - Suppress all information messages.

 COUNT_ROWS  - Update the row count column.

The table/view and indexes may be specified by 'name' (in single quotes) or ID.

If 0 is specified, the current database will be used.

Example

DBCC UPDATEUSAGE ('MyDatabase','MySchema.MyTable');

GO

 

I am trying to make a Stored procedure related it. The Stored procedure is ready to use.

 

 

/*

   Date: 02-April_2012

   by : joydeep Das

 

*/

 

IF EXISTS (SELECT *

           FROM   sysobjects

           WHERE  type = 'P'

                  AND name = 'up_RUN_UPDATEUSAGES')

      BEGIN

            DROP  PROCEDURE  up_RUN_UPDATEUSAGES

      END

GO

CREATE Procedure [dbo].[up_RUN_UPDATEUSAGES]

      (

        @P_DBName   VARCHAR(MAX) = NULL,

        @P_TBlName  VARCHAR(MAX) = NULL,

        @P_IndxName VARCHAR(MAX) = NULL   

      )

 

AS

 

    DECLARE  @v_TblName VARCHAR(MAX)

 

BEGIN

       IF ISNULL(@P_DBName,'')<>'' AND ISNULL(@P_TBlName,'')='' AND ISNULL(@P_IndxName,'')=''

          BEGIN

               EXEC ('DBCC UPDATEUSAGE('''+ @P_DBName +''')')

          END

       ELSE

          BEGIN

            IF ISNULL(@P_DBName,'')=''

               BEGIN

                   PRINT 'Database name can not be blank'

               END

             ELSE

               BEGIN

                   IF ISNULL(@P_TBlName,'')<>'' AND ISNULL(@P_IndxName,'')=''

                      BEGIN

                          EXEC ('DBCC UPDATEUSAGE('''+ @P_DBName +''''+','''+ @P_TBlName +''')')

                      END

                   ELSE

                      BEGIN

                          EXEC ('DBCC UPDATEUSAGE('''+ @P_DBName +''''+','''+ @P_TBlName +''''+','''+ @P_IndxName+''')')

                      END  

               END     

          END  

END

 

 

 

GO

 

 

 

-- TO run this for Specified DB, Specidfied Table, Specified Index

 

EXEC up_RUN_UPDATEUSAGES

     @P_DBName   = 'MY_DB',

     @P_TBlName  = 'MYTAB',

     @P_IndxName = 'INDX_MYTAB_1'   

 

-- TO run this for Specified DB, for All Table Objects

 

EXEC up_RUN_UPDATEUSAGES

     @P_DBName   = 'MY_DB',

     @P_TBlName  = '',

     @P_IndxName = ''

 

 

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS

 

No comments:

Post a Comment