Know When to Run DBCC UPDATEUSAGE In SQL Server
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:
- The free space remaining on disk for the growth of data.
- The current size of files present in database.
- The space available within the files of database.
- The growth rate of files and the maximum file size.
Table of Content
Tools that Ensure the Accurate Space Allocation
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
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:
Running sp_spaceused with DBCC UPDATEUSAGE
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.
Also Read: SQL Server Lookup Table
What If The problem is Still There Despite the Above Methods?
Now, if users are already tried this way & still they face storage issues in their SQL Server, then something might be very wrong with the database. In most cases, when this command doesn’t work, there are just two meanings:
- Genuinely No Storage Left
- Database corruption is Filling Up Space
Either the storage is genuinely filled up or there must be database corruption issues in the server. In case the database is full naturally, users have only a few options like delete data or upgrade storage.
However, in case the files are corrupted, they behave weirdly & occupy more space in the database. For this issue, getting a reliable SQL Recovery Tool is what users need to do. Download the tool to solve this problem as well & you are good to go.
Conclusion
Finally, we are at the end of this article after discussing all the possible scenarios as well as solutions for this problem. All that users need to do is understand the entire situation calmly & react with the most relevant & efficient solution to get rid of the storage issues.