3

I am running a rather large database on SQL Server 2008 R2 Express. Recently I hit the 10GB limit, so the system would not allow me to create anymore objects, ect..

I have archived off a very large amount of records, around 3 quarters of the data that was there.

The database file is still around 10GB though, I was wondering if there is anything I need to do to release that free space in SQL Server.

8

If you are talking about the file system space and releasing it back to the Operating System, then you will have to manually do this operation.

First find out how much free space you have in each file:

use YourDatabase;
go

;with file_cte as
(
    select
        name,
        physical_name,
        size_mb = 
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb = 
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    physical_name,
    size_mb,
    space_used_mb,
    free_space_mb = size_mb - space_used_mb,
    space_used_percent = 
        convert(decimal(5, 2), space_used_mb / size_mb * 100)
from file_cte;

Then if you are absolutely sure that you need to shrink a database file, you can use DBCC SHRINKFILE().

To shrink your file, an example would be like this:

dbcc shrinkfile(YourDataFileName, <target_size_mb>);

But... only shrink your database files if you absolutely must. If your files are going to re-consume that same space in the future, it doesn't make sense to shrink your files. A necessary read is Why you should not shrink your data files by Paul Randal.

  • Excellent thank you, I will give that article a read before I do anything.. – Alec. Dec 16 '14 at 14:10
6

Rebuild the tables that are there - that will let SQL Server reorganize everything to maximize the amount of space within the file that can be reused.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
ALTER INDEX ALL ON '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'
  REBUILD WITH 
  (
    FILLFACTOR = 80, 
    STATISTICS_NORECOMPUTE = ON
  );'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

You may want to look into other REBUILD options, or - if, say, you have indexes with different fill factors - make it dynamic so that it rebuilds each index with its existing fill factor. Lots of potential changes to the script, but that should form the basis of not having to do it manually.

There is no reason to shrink the file because that just means it will have to grow again when you start adding more data. You're going to hit the Express limit when the file needs to grow beyond 10 GB regardless of how many times it had to grow in the meantime. These posts have some good information on why shrinking is wasteful because, in most cases, you're just going to have to grow again:

I Need to Shrink My Database - I just freed a lot of space

Why Does the Transaction Log Keep Growing or Run Out of Space?

  • Can this be done on a live environment? – Alec. Dec 16 '14 at 14:20
  • Well, there will be blocking, since Express does not support online rebuilds. But your data size is < 10 GB so, I can't imagine it will be an extremely serious problem unless the hardware is horrible. If you're worried, do it overnight, take a maintenance period, go from smallest table to largest, etc. – Aaron Bertrand Dec 16 '14 at 14:25
4

Think of SQL data files like a big storage box. The file is set to certain size, but that doesn't mean that all the space will be used. It's a container for your data/indexes/objects/etc. You're not just thinking about the data that is there now, you want to think about how big your data will grow in the future. If you archived off 3/4 of the data, you may just want to leave the file as it is since, over time, that space will only get used again.

Shrinking DB files is advised against since

1) The files got to that size for a reason and will fill up all over again, causing auto-grow events. These auto-grow events can be taxing on the I/O system when they occur, even with Instant File Initialization enabled. and

2) When you shrink a file, you can cause severe fragmentation on the indexes as the shrink operation takes the data from the end of the file (in a contiguous state) and reshuffles it to the open space earlier in the file (in a non-contiguous state). This can cause some serious performance issues.

  • 1
    Well, Express has a 10GB limit on data files, whether you have a DBA or not. – Aaron Bertrand Dec 16 '14 at 14:05
  • Ahhh, yes it does. He said SQL 2008R2 so I assumed he wasn't talking about express. Answer updated. – Kris Gruttemeyer Dec 16 '14 at 14:06
  • Apologies, I did mean express. – Alec. Dec 16 '14 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.