The Microsoft SQL Server does not provide the correct information about the database allocation space. The database with higher number of online transaction results inaccurate space allocation report.
Database Administrator performs number of transactions on database that results growing and shrinking of data present in Server. When data stored in server exceeds its limits, it causes Out-of-space situation. Therefore, it is very necessary to get the status of allocated space.
Some monitoring tools will make it quite easy to know the status of SQL Server Space allocation. DBCC UPDATEUSAGE is one of the tools that have been used for getting the accurate status of allocated space in SQL Server.
In this article we will discuss the usage of DBCC UPDATEUSAGE to know SQL server space allocation.
For capturing SQL allocated Space usage, DBA must consider the following point in order to resist the Out-of-Space condition:
If the table space and row count information are maintained accurately on database with every operation (INSERT/ DELETE) or in concurrent transactions, the system will capture the correct space information in real time.
To ensure the correct information about the allocation space in SQL Server, there are the tools like sp_spaceused and DBCC UPDATEUSAGE that will scan the all pages of database and update the allocated space information.
The tool sp_spaceused is a system-defined procedure, which generates the report on total rows present in table, index space used and space used by table.
Also, the tool will generate the summary report on total disk usage for the database which will help DBA for disk monitoring and disk capacity planning for future operation to be performed on database.
he syntax of sp_spaceused is given below
DBCC UPDATEUSAGE reports and rectifies page and row count inexact values present in catalogue view. These inaccurate values are the result of inaccurate space usage report generated by sp_spaceused.
DBA can run DBCC UPDATEUSAGE standalone before running the sp_spaceused or can be run as a parameter to sp_spaceused.
The syntax of DBCC UPDATEUSAGE command is given below:
As discussed above the number of rows reported by sp_spaceused is inaccurate. By using DBCC UPDATEUSAGE with sp_spaceused it will update space allocation information DBA can use given command syntax.
To get the accurate count of rows present in table on needs to mark parameter “@updateusage = ‘true’”
exec sp_spaceused @updateusage = ‘true'
In addition, there is a limitation of this procedure is that it will does not update the row count information. To overcome the situation of not updating the row count, DBA can run COUNT_ROWS with DBCC UPDATEUSAGE firstly and then run the sp_spaceused without running the @updateusage parameter. By using these system-defined tools, DBAs can get the accurate information about the SQL Server space allocation.