15

This question already has an answer here:

I have an SQL Database with a lot of unused space. However when I run Tasks | Shrink | Database And Tasks | Shrink | Files from Management studio it isn't freeing up any space.
Unfortunately the machine that it is running on is running out of hard disk space. Any ideas on why this space isn't freeing up.

Also recovery mode on DB is set to simple. enter image description here

Here is the unused dialog that comes up when I go to shrink files: enter image description here

I've also tried doing this via T-SQL

DBCC SHRINKDATABASE (myDatabase, 5);

I get following response:

DBCC SHRINKDATABASE: File ID 1 of database ID 6 was skipped because the file does not have enough free space to reclaim. DBCC SHRINKDATABASE: File ID 2 of database ID 6 was skipped because the file does not have enough free space to reclaim. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

marked as duplicate by Paul White Sep 10 '15 at 19:51

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Oct 16 '13 at 21:47

This question came from our site for professional and enthusiast programmers.

6

I'm going to make a wild guess and say that your database is set up to use FULL recovery. You have never taken a log back up (let alone a full one) based on the image above. If this is the case then you log file is growing and growing and can't clear out because you haven't taken a backup.

If you have the room take a full backup, then a log backup and try your shrink again. Should work fine.

Worst case (and I do mean worst, particularly if this is a production database) is to set the database to SIMPLE recovery, shrink and then take a backup. This should work even though you don't have enough space for a shrink normally.

At this point make sure you set up regular backups for your database.

You can go here for more detail.


EDIT:

Can you tell us what % free the log is vs the data file? Also you might look at the log_reuse_wait_desc in sys.databases.

SELECT name, log_reuse_wait_desc FROM sys.databases 

And last but certainly not least have you tried the TRUNCATEONLY option of shrink?

DBCC SHRINKFILE (Adventureworks2008R2_Log, TRUNCATEONLY)
5

Well this could have many reasons. I will mention all possible reasons and afterwards define solutions to solve the issue.

  1. As your DBCC output mentions, you have not enough disc space available to shrink the database file. In other words. You try to move pages from your database file to a newly created file. During this time, the pages exists two times. Afterwards SQL Server will remove the old database file and take the newly created file for production use. You may have to increase your disc space or follow the solution mentioned below.
  2. Another point which will prevent shrinking, may be a database lock (which isn't your case). If the database is locked during DDL changes it won't be able to shrink the database file. If you shrink the file with the wizard, you probably run into a timeout message from time to time. If you run it as a script, it normally would wait for ever till the lock is released and then shrink the file.

Possible solutions

The easiest solution is planning! Check your database and try to evaluate how much data will be inserted per day, determine the growth rate. By default, SQL Server will grow a file by 10%. This isn't bad for databases which holds just 100MB as the growth just would be 10MB. But if your database gets bigger and biger, the growth will cost many resources and time (100GB -> 10 GB, 1TB -> 100GB etc.). Define a growth factor in MB. It shouldn't be to small (e.g. 1MB) as it will stress your disc on big transaction and will slow down your operations, but not to big to cause a huge write operation (e.g. 10GB).

Additionally define a growth limit on databases which may escalate (e.g. Logging databases). The transaction may fail if the limit is reached, but it won't hurt to much on logging databases. But if your logging database uses up all your available disc space, it may cause a transaction abort/rollback on a production database.

If you have the issue, that your disc space is too small and won't be able to hold the used disc space again (for example: 140GB file, 40GB free, means 100GB to reorganize -> you need minimum additional 100GB free space for the time of the shrink on a moving operation), you still can get a bit of space back by using the TRUNCATEONLY flag.

DBCC SHRINKFILE (1,TRUNCATEONLY)

This will cut off all empty data pages which are hold back at the end of your database file with the file_id = 1. Back to our 140GB example: If your 140GB database file holds the last written page at the position of 120GB you would gain the last 20GB nearly instantly back. This process is quite fast, as it just moves the file end marker to the position of 120GB and will inform your operating system about the free space.

You can get a quick overview of your database files using this query:

SELECT *
FROM sys.sysfiles

If you have enough free disc space (or after you've run the TRUNCATEONLY on every file), you should be able to really clean up your database files and really shrink them.

This could be achieved by using the following statement:

DBCC SHRINKFILE (1);

If you have a database table which is often written or runs often bigger transactions, I would suggest to define a target limit. This depends on your experience with this database. Normally 5-10% (depending on the size of the database), but minimum 100-1000MB should be set. As example on our 140GB database file (40GB empty) the target size should be set to 110GB (100GB used + 10% reserve).

You can achieve this by using this statement:

DBCC SHRINKFILE (1,112640); -- 110GB in MB

But beware, this could run very long (depending on your file size and your I/O subsystem). As you will create a bigger workload on your I/O subsystem, I would suggest to run such scripts in low load times (maybe overnight).

If you really want to clean your database files, it would be a great idea to check all your indices and REBUILD or REORGANIZE all indexes which have a high fragmentation.

If you are on a development machine or are really willing to take a risk you can run this statement. It will iterate over every index in your database and rebuild it.

DECLARE @sql nvarchar(max), @sch nvarchar(max), @obj nvarchar(max), @ind nvarchar(max)

DECLARE cur CURSOR FOR
    SELECT s.name, o.name, i.name
    FROM sys.objects as o
    INNER JOIN sys.schemas as s
            ON o.schema_id = s.schema_id
    INNER JOIN sys.indexes as i
            ON o.object_id = i.object_id
    WHERE i.type > 0 

OPEN cur

FETCH NEXT FROM cur INTO @sch, @obj, @ind

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @sql =  N'ALTER INDEX ['+@ind+'] ON ['+@sch+'].['+@obj+'] ' 
                + N'REBUILD PARTITION = ALL '
                + N'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, '
                + N'ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
    PRINT(@sql) EXEC (@sql)

    FETCH NEXT FROM cur INTO @sch, @obj, @ind
END

CLOSE cur
DEALLOCATE cur

After that process, your database file may be grown again. But you will be able to shrink it to a much lower file size as the free space in every index is removed.

Warning and additional information

Be careful in shrinking your database files. It has a good reason that SQL Server allocates file space in advance. Disc allocations are expensive, doing them as less as possible is a good idea. If you have a very agile database which produces every day an overhead of a few GB and release it after some time, it won't be good to shrink the file always back to the minimum. You waste disc power to grow the file, do your stuff and waste disc power again to shrink the file back again. In such cases, be aware of wasting a bit disc space to improve your data operations.

Hopefully this long summary will help you at your problem and even help you to make a plan for your databases.

  • On SQL Server 2017 the query tries to include some system tables. Adding "AND o.is_ms_shipped = 0" below "WHERE i.type > 0" fixed the problem – nh43de Dec 9 '18 at 15:35
4

You must also Shrink the files to retrieve the disk space : Tasks | Shrink | Files in SQL Server Management Studio

You need to check the Option "Reorganize files before releasing unused space", and set a percentage of free space, depending of the expected growth of your database.

3

Before shrinking you need check Page fullness of clustered index of your biggest tables

Page fullness and fragmentation[Page fullness and fragmentation]

Make Rebuild it Rebuild clustered index[Rebuild clustered index]

Shrink data file : Tasks | Shrink | Files in SQL Server Management Studio with "Reorganize files before releasing unused space"

P.S. Please notice that after Shrink you will get highly fragmented database that can affect performance

  • +1 Thanks a lot for this. I've been banging my head for two hours trying to figure out why neither DBCC SHRINKFILE nor DBCC DATABASE would shrink the ASPState database (2 tables, <100 rows). Your suggestion did the trick; I've rebuilt all indexes and the data file shrank from 2 GB down to a mere 6 MB! – Daniel Liuzzi Feb 9 '17 at 12:01
2

Check the initial size setting in Properties-->Files. Initial should read minimum, you cannot shrink below it no matter how much is free space.

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