Well this could have many reasons. I will mention all possible reasons and afterwards define solutions to solve the issue.
- 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.
- 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.
[Page fullness and fragmentation]
[Rebuild clustered index]
