I recently came across an issue where one of our database servers had exhausted it’s drive space and the application had failed due to this. In this instance, it happened to be a server running one of our SolarWinds monitoring databases. We had recently extended the disk space by 50GB and this was consumed in a very short amount of time. This was surprising considering the number of endpoints this particular system monitors so I wanted to figure out where the capacity was going.
We can see that the primary MDF database file is consuming the vast majority of space –
Having identified the file I wanted to see what in the database was using all this space. As I mentioned above, we increased the capacity and it was gone in a flash. I decided to check if the database just had a large amount of empty space by check if it could be shrunk. While there was a very small amount of space it was not significant as we can see below.
Let’s take a look at the top tables by disk usage – this may help us understand where our space is being eaten up. To do this we’ll open up SQL Server Management Studio (SSMS), right-click our database and browse to the desired report.
Once the report completes we see that the dbo.SysLog table seems to be our biggest consumer.
Speaking with my colleagues it became apparent that the syslog data was not really being reference or used and as such they were happy for us to reclaim the disk space. To achieve this we can either delete records prior to a specified date or we can just truncate the entire table.
If we wish to remove data prior to a specified date we can use the following code –
Delete from Syslog Where datetime <= '10/7/2019'
If we wish to simply truncate the entire table we can use the following code –
Truncate Table Syslog
Having truncated the entire table we can now check if the database can be shrunk –
Well look at that – 98% of the circa 250GB database can be reclaimed. It looks like the syslog component has certainly been eating up drive space! We now have to decide whether to leave the database as is, which is perfectly OK if you want to but if you wish to remove the drive space alert in Windows or alerting systems you’ll need to shrink it.
This can either be done in the shrink file interface above by selecting you wish to shrink to a specified file size or you can define a new initial file size for the database and then complete a ‘Release unused space’.
I hope you find this information useful – you never know what issues you may be asked to look into so having a familiarity in a wide range of fields is incredibly handy.