Shrink an SQL database

Size does matter

As you use a database, the log files increase in size. When you back up your database, SQL frees the space in the log file – it doesn’t shrink it – and the process starts again, filling the log file.

In typical production environments, you run backups, but in test environments, you may never run backups, so the log files will keep growing until you do something about it.

Regardless of where you run your database, the larger the database, the more it costs. This is very much the case in cloud environments; the larger the database, the more you pay for storage, data ingress, and egress. However, even if you host your own database, you still have to pay for storage costs through disk space. The more storage space you need, the more SSDs or HDDs you need.

So, keeping database sizes in check is essential regardless of where you store them. Fortunately, it is easy to do this – just read below: –

Shrink

There are a couple of things you can do. On databases that I know I will not be backing up, I set the Recovery Mode to SIMPLE like this: –

ALTER DATABASE MYDatabase
SET RECOVERY FULL;
GO

You can always put the database back to normal with this snippet: –

ALTER DATABASE MYDatabase
SET RECOVERY FULL;
GO

However, I often forget to set the database into SIMPLE recovery mode, or I restore the database from somewhere else, and it is in SIMPLE mode. This means that the log file will grow until it becomes a problem. So, in those instances, I change it to SIMPLE mode and then shrink the log file like this: –

USE MyDatabase
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 100);  -- here 2 is the file ID for transaction log file,you can also mention the log file  name ,(dbname_log)   and 100 is the number of MBs.  If < min required, it does not shrink at all, so make sure this is big enough
GO

If I want FULL recovery mode back on after this – which I usually don’t – I just enable it with the snippet above.

Related posts:

Update SQL Statistics on things Enable SQL Database Diagrams Debugging Dot Net Core applications on a Raspberry Pi with Visual Studio Delete Obj/Bin folders in a Visual Studio solution