A very common problem for database administrators (DBAs) is limited disk storage space. Whether for budget or infrastructure reasons, it is not always possible to increase the storage on our servers quickly. Because of that we need to take effective action on this kind of problem, to save ourselves a bad time dealing with a database outage.
Here are three simple recommendations for tackling the runaway growth of your data files (*.mdf and *.ndf) in SQL:
a) Identify the heaviest tables
For this we can use a report SQL Server Management Studio offers called 'Disk Usage by Table'. It is very useful because it gives us the size of every table in the database, along with the number of rows and the space allocated on disk.
We can run into two scenarios:
The tables are spread in a balanced way across the space the database occupies.
There are tables taking up most of the space the database uses. If we are in scenario 2, it is advisable to implement a data retention policy in line with the company's own policies.
There will also be cases where it will be necessary to create a historical database to store all the data from those tables that goes beyond the retention period.
b) Compress the data files
In some cases the data file can be compressed because not all the space allocated on disk is being used. That can happen after data clean-up — for example, after implementing the retention policy — or when large objects have been removed from it.
We can also check this with the help of the report mentioned above. Another useful report for this is 'Disk Usage', which will show how much space is unallocated and can be reclaimed to free up disk space.
Once we have identified that there is a considerable amount of space going unused, we can shrink the relevant data files.
c) Move the data files elsewhere
According to SQL Server best practice, data files (.mdf) and log files (.ldf) should be stored on separate disks. That way we can stop unexpected log file growth eating into the space allocated for the data files. If that is not the case, we recommend relocating the data and log files respectively. It will require a restart of the SQL Server service for the changes to take effect.
In environments where we have enough storage disks, it is advisable to have one disk per database and per type of data file (.mdf and .ldf). That way we can isolate one database's space problems and prevent them affecting others on the same instance or server.
These are only some of the recommendations we can implement in our database environments. There is a wide variety of solutions to be found online, but in the end it all depends on factors such as how critical and how transactional the database is, the budget, and others.
