Working out why a SQL Database has grown over the limit

So I ended the previous post with

Oh, and if you're wondering, the issue was that their SQL Express had hit the 10gb limit, all because the space cadet developer had decided to save images into the database. I might do another blog post on how I figured this out as it would be helpful for beginners in the "Emergency SQL DBA" space. 

So here is how I was able to see (quickly) that it was caused by the images:

As part of my SQL Integrity Check, I poke around the tables and look at the DB size etc. I must confess that for a lot of this, I rely on scripts written by Pinal Dave.

The first thing I did though, and remember that I was talking to someone who works in construction and by his own admission doesn't know much about computers, is find out what version of SQL Server this company is using. You can easily do this by running:

SELECT @@VERSION

When I ran this I was able to see that they're running SQL Express. And of course this version of SQL has a limit of just 10Gb per database (more on that below).

I then right-clicked the database name and saw that yep, it had hit 10Gb in size and this therefore aligned with what the business owner had told me was what the error message was saying. In his words "it's saying we're out of space".

So now it's the challenge of finding out why it's hit the 10Gb limit. Has it been incrementally growing at 10mb a day for years? Or was it a sudden thing?

I moved onto this script:

https://blog.sqlauthority.com/2021/02/12/sql-server-list-tables-with-size-and-row-counts/

And almost immediately after pressing F5 on that, the issue was obvious.

The #1 ranked table was one called "Images" ... Out of the 10Gb it was taking up 9 of them!

Next I ran this:

sp_help Images

This showed me the column names and the types.

And to the surprise of absolutely nobody, there was a field called ImageData that was defined as VARBINARY(MAX). There was also a field called ImageCreateDate.

Therefore I ran this:

SELECT TOP 10 * FROM Images ORDER BY ImageCreateDate

At the time of writing it is September 2024. There were images going back to 2011 ... Thousands of them.

The business owner assured me that they had tried deleting the data from the front end. So I then explained how soft deletes work etc ...

He told me that he only needed the past 2 years of data and as he needed the system up ASAP he told me to blow the old ones away.

Now, since I am a Nervous Nelly from having working in this field for over 20 years, I did a backup first. I created a new database called RecycleBin, and copied over 15 years worth of these images to that database.

And then I did the delete.

And then the system was working again.

It's worth remembering that a SQL Server Express database is limited to 10Gb, but you can have multiple databases on the same instance. So if you need to delete data, and still have a relatively easy "undo feature", then this is a way to do it.

I hope you found this useful.

Rodney