Sql-server – Release unused space in SQL Server database

shrinksql server

I have a SQL Server database with roughly 1.5 TB test data (size of core tables are calculated using sp_spaceused 'tablename'). At the time of data creation process, I have allocated 350 GB to each data file, and my database contains 9 data files and 3 log files.

I want to shrink the database and release unused space before taking a back up. In the database shrink options it shows Available free space as 51%.

Using Management Studio tasks-> shrink options:

I shrunk individual data files and log files.
then, shrunk the database.

But still shows as 51% free space is available. Can anyone tell me what I'm doing wrong there or am I missing any steps on shrinking process?

What I'm looking for is, since there is a lot of unused space in the database, I want to release it, so the database size gets reduced.

Best Answer

You can script the SQL commands that SSMS generates. Without us knowing what commands you execute, we don't have much to go on.

Also, pls be specific about each file and the file size. You have 12 files. How large was each file before the individual file shrink, and what was the file size after the individual file shrink?

Did you use the TRUNCATEONLY options of the DBCC SHRINKFILE commands? That is the default in the GUI.

Finally, no need to do a "final" DBCC SHRINKDATABASE. Work with each file separately instead.

Related Solutions

Sql-server – Shrink database is not shrinking as much as expected on a 1.5 TB database.

There will always be some free space in the database, stored within pages that are too full for other data. Shrinking databases is an incredibly intensive operation, and will take a lot of time as it moves almost every record to somewhere new. A terabyte is a lot of data. Interestingly, you may have more luck creating space by applying compression such as the Hyperbac stuff from Red Gate. That can help remove the free space and compress the rest, but potentially doing fewer writes (and certainly not having to do lots of log activity) in the process. Your mileage may vary though - I don't want to try to speak authoritatively on how Hyperbac compression of your data will work without knowing much about your data. Hope this helps though...

Sql-server – Free space of mdf and ldf does not match with database free space

This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the data files, not the log.

Let's coalesce some things.

Database properties and shrink database show the same thing (not that you should ever be in the shrink database UI anyway!).
Database file properties show 17 + 75 = 92 which, with rounding before addition, is probably the same 91.31 in 1.
For space allocated, shrink for individual files show 16.38 + 74.94 = 91.32 - again, probably some rounding there, otherwise exactly matching 1.
For space available, shrink for individual files is the only place where I suspect a real discrepancy, and this is because the UI is inconsistent about where it gets its data, and some of these places are subject to the caching that necessitates DBCC UPDATEUSAGE.

Let me take a look at what these different dialogs run for my local copy of AdventureWorks2012 (with certain tables enlarged from this script).

EXEC sp_spaceused;

This returns (first resultset only):

database_size    unallocated space
-------------    -----------------
   1545.81 MB          6.67 MB

Essentially runs this, which - I've confirmed via trace - is roughly the same query executed from the database properties and database shrink dialogs (I've carved out the irrelevant parts from the stored procedure, and added an outer query to represent the math that SSMS does for display):

SELECT database_size = DbSize*8.0/1024 + LogSize*8.0/1024,
  [unallocated space] = (DbSize-SpaceUsed)*8.0/1024
FROM
(
  SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
       WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    SUM(a.total_pages) AS [SpaceUsed],
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
       WHERE df.type in (1, 3)) AS [LogSize]
  FROM sys.partitions p 
    join sys.allocation_units a on p.partition_id = a.container_id 
    left join sys.internal_tables it on p.object_id = it.object_id
) AS x;

This returns a match:

database_size    unallocated space
-------------    -----------------
    1545.8125             6.671875

These dialogs all show this information correctly. Database Properties dialog:

Shrink Database dialog:

The shrink file dialogs, on the other hand, run a slightly different query (again this is carved/adapted for convenience):

SELECT SUBSTRING(name, CHARINDEX('_',name)+1, 4), 
  [Currently allocated space] = size/1024.0, 
  [Available free space] = (Size-UsedSpace)/1024.0
FROM
(
  SELECT s.name, 
    CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
    s.size * CONVERT(float,8) AS [Size]
  FROM sys.database_files AS s
  WHERE (s.type IN (0,1))
) AS x;

_{Note, too, that in addition to getting size data from a function instead of a DMV, the predicates have not been updated for new file types, like filestream/hekaton.}

Results:

        Currently allocated space    Available free space
----    -------------------------    --------------------
Data                         1517                  7.9375 -- wrong
Log                       28.8125               25.671875 -- wrong

The problem is the FILEPROPERTY() function, which is not guaranteed to be up to date (even after DBCC UPDATEUSAGE(0); is run; more below). This ends up with this misleading information on the dialogs:

Wrong space available readings

Note, again, that 6.67 MB was never really accurate, since this is only measuring the total database size - the number of pages allocated, completely disregarding the log.

In all honesty, if you want accurate reporting of space used in the database, stop using the mickey mouse UIs which run all kinds of different queries to figure this out, and stop using the shrink file dialogs for retrieving information. These are clearly subject to stale data problems in certain cases. Run an actual query against a source you can trust. Here is what I prefer:

DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p 
INNER JOIN sys.allocation_units AS a 
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
( 
  SELECT [type] = CASE 
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END, 
    size*8/1024.0 FROM sys.database_files AS f
)
SELECT 
  file_type = t, 
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END 
FROM x;

This query returns three numbers that should look very familiar, and one that should not:

file_type    size           available
---------    -----------    ----------
data         1517.000000     6.6718750
log            28.812500    17.9008512

Note that DBCC SQLPERF is also slightly prone to issues with space usage, for example after running:

DBCC UPDATEUSAGE(0);

The above query yields this instead:

file_type    size           available
---------    -----------    ----------
data         1517.000000     8.0781250
log            28.812500    17.8669481

sp_spaceused now yields matching numbers as well (1545.81 MB / 8.08 MB), even though - again - that is only the space available in the data file(s), and the database property and database shrink dialogs are "accurate" as well (but the shrink file dialogs are still way off - FILEPROPERTY() does not seem to be affected by UPDATEUSAGE at all):

Database Properties dialog after updateusage