Here’s a real world scenario that database administrators face on a regular basis. Your application moves along smoothly; the amount of disk space increased incrementally each day. Then, all off a sudden, your database disk allocation jumps 20% or more with no apparent reason. Do you simply acquire more disk space, or do you find out if perhaps you need to do something else first?
Database users are notorious for keeping data around for long periods of time because they may want to look at it later. That’s a topic I’ll pick up tomorrow and drill into a little bit. If you are on an application that starts out small, and increases in popularity, your daily disk utilization can simply grow as your customer base grows.
Sometimes there is increased activity relating more to customer adoption of your application causing the increase in disk utilization rather than increasing customer count. As features become more rich customers have a tendency to create more data.
Perhaps indexes have been added to some of your tables to optimize performance. If covering indexes are created they can take nearly as much data as the original table itself. So, if you have recently had a software release, you might look into the indexes created.
Another cause may be the process of defragmentation. If the clustered index of a table or any non-clustered index is larger than the space available in tempdb, then when that index is defragmented by rebuilding it, a new index is built inside the database where the original index resides. If the index uses a lot of disk space, the database must have enough available space to create a new instance of the index. Once the new index is built your database will have a lot of allocated, unused space.
One thing I find useful is to keep a running snapshot of allocated disk space by index or heap in a table provided by the system view sys.dm_db_partition_stats. You can join this table to sys.indexes and sys.objects to get further information about the view contents. What I find useful is the separation of data by file object (if you use multiple data files for your database).
With this snapshot I can identify the specific table and/or index that has acquired space, and determine what I need to do to make sure I don’t run out of disk. These are some things that may be specific to SQL Server…but the real life situation applies to any database engine, even noSql storage.
Why not share your strategies for handling increased disk consumption. You don’t have to restrict your comments to SQL Server only strategies. This is a problem any System Administrator will face eventually. You can add comments here or drop an email to btaylor@sswug.org.
Cheers,
Ben