You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.
First try and "shrink space" for each affected table:
alter table fragmentedtable enable row movement;
alter table fragmentedtable shrink space;
Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.
The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
For example:
FILE_NAME SHRINK_TO CURRENT_SIZE SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf 696 700 4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf 579 610 31
/u01/app/oracle/oradata/PHIL112/users01.dbf 85 93 8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf 65 120 55
SQL>
To resize, use:
alter database datafile '/path/to/data/file.dbf' resize 100m;
The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.
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:
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):
Oh, and might as well show what Windows Explorer thinks of these files, so you can relate to the calculations made to determine MB:
How accurate this all needs to be, of course, depends on what you're going to do with the information.
Best Answer
Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow.
SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that if you've used that space once, you'll use it again. Autogrow can be an expensive event and unnecessary if you've only freed up space temporarily (what were you able to do with all that free space in the meantime?). For the same reasons, you shouldn't try to temporarily reclaim space, either. Just let SQL Server use the 18 GB of available space as you add more data. If you think you will need more than 18 GB of additional space going forward (in which case, you will need to add file(s) on other disks, or move to a bigger disk).
sp_spaceused
(and in turn the UI dialog you're looking at) may return more space than is possible because of synchronization issues in the metadata about your tables/indexes/files. In order to make sure it reflects accurate space, run this:I have also seen scenarios where rebuilding indexes was required in order to rectify the counts/space, but I haven't seen that specific case since SQL Server 2000.
(I suspect this isn't a simple case where your database already spans multiple disks, or you surely would have mentioned that in the question.)
That all said, the fact that when you shrink the data file expands almost immediately leads me to believe that you are actually using the space, but must also be performing big deletes or updates that are freeing it up (which is when you see 18 GB free). Unfortunately it's impossible for us to see exactly why the data file is expanding and then clearing itself out - perhaps you have transactions where you are truncating / re-populating large tables, performing massive archive operations, etc.