Disk Usage

Disk usage in SQL can mean many things. Simply put, disk usage is measuring what data structures may exist in a database, memory allocation for the medium storing that data, and the relationship between the two. SQL has a number of scripts and commands that can give a database manager (DBM) different types of information about how the disk is being utilized.

The most basic commands can tell a DBM how much memory is allocated or unallocated, and the number of tables making up the allocated space. The more complex scripts can provide visual representations of each table in a database with the amount of data, number of rows per table, and where it is located on the media. Some commands and scripts can provide information on the space that is not being used and provide predictions of how much disk space potential data will require.

Disk usage commands and scripts can also be used for monitoring systems, providing necessary reports and warnings to a DBM. These scripts provide the DBM information on the health of the disk, potential problems, and alerts for when available memory is becoming low or average table size may require additional servers and disk space.

For instance, a very simple command that can be used in Transact-SQL is “sp_spaceused.” It is recommended to run this script with the “updateusage” value. The updateusage value corrects any errors in row or page counts in the catalog views of a database and individual tables.

To identify what level and type of information you want, you can add an “objectneame” value to the command line. If you do not include an object name, the resulting return is information on the whole database. By including objectname and the name of a table in the command line, you will receive table specific information.

The information returned will be very simplistic, but still useful. If you query the whole database, you will receive information on the database name, allocated and unallocated memory, space allocated to objects, data, indexes, and space reserved for objects. If you query an object name, you will receive the same information with three exceptions. The information will be specifically about the table queried, but you will not receive the database size or unallocated space, and will receive the number of rows in a table.

There are too many options, scripts, and capabilities for disk usage queries to go into here. There are a lot of resources available to perform more in depth queries, as well as third-party applications that provide GUIs and dashboards.