Reduce DB size – SQL Server, SSMS

One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the size of the database (100gb) so i cleared it. I ran a script to check the table sizes in this database and everything went back to normal. That is, no ridiculously fat table. However, the general size of the database didn’t change despite the diet to the problematic table.

Any suggestions?

BTW, I am using SQL SERVER 2012

thanks, David

I would be willing to bet that your database is full recovery and that you are not taking transaction log backups. Log backups are required for the transaction log to clear and reuse space. Otherwise it will just continue to grow and grow.

SQL Server files are essentially containers to hold your data, like a bucket. This bucket, of course, will grow to accommodate your data within it. However, once it grows, it will remain that size unless you shrink the file itself, even if you empty out data. Like so:

You can see how much space is empty in your database files with the following queries(borrowed from Glenn Berry’s DMV script:

SELECT f.name AS [File Name] 
, f.physical_name AS [Physical Name]
, CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB]
, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) AS [Available Space In MB]
, [file_id]
, fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
   LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
   ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

To reduce your file size, you’ll need to use the DBCC SHRINKFILE command to actually reduce the file size. However, keep the following caveats in mind:

1. You can’t shrink the file beyond the point that you have data in your file (so if you have 10 GB of actual data, you can’t shrink it beyond that).
2. Be mindful of whether or not you actually need to shrink. If your data volume will grow to this size again, you are not helping your database by shrinking those files. Work has to be done by both SQL Server and the host OS to grow a data file out. Paul Randall talks more about it here.