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