This question is asked in various forms here but the question boils down to:
I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.
Some Initial Caveats:
If you've read about the concerns and risks and you still need to do this shrink because you freed a significant amount of space, hopefully the rest of this answer will help you out. But do consider the risks.
There are two main approaches two consider here:
1.) Shrink Yes, do the actual shrink - Consider using DBCC SHRINKFILE instead of DBCC SHRINKDATABASE, you have more control over what gets shrunk and how. This will cause some performance degradation for sure - it is a large operation doing a lot of IO. You can potentially get away with repeated shrinks to a target size that gets progressively smaller.
This is the "A.)" example in the above DBCC SHRINKFILE link.. A datafile is being shrunk to 7MB target size in this example. This format is a good way to shrink repeatedly as your downtime window allows. I would do this in testing on development to see how the performance looks and how low/high you can go of an increment and to determine the expected timing in production. This is an online operation - you can run it with users in the system accessing the database being shrunk, but there will be performance degradation, almost guaranteed. So monitor and watch and see what you are doing to the server, pick a downtime window or period of lighter activity, ideally.
USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO
Always Remember: - every time you shrink you fragment your indexes and should do an index rebuild if you are going to shrink in chunks over a prolonged period of time. You are now incurring that cost each time if you can't get it done all in one window.
2.) New Database - You could create a new database and migrate data to it. You would have to script the empty database out and all of it's keys, indexes, objects, procs, functions, etc. and then migrate data to it. You could write scripts for this or you could use a tool like SQL Data Compare from Red Gate or other vendors with similar tools. This is more setup work on your side, more development and testing, and depending on your environment may also blow out your downtime window also but an option to consider.
When I am forced to shrink a Database If this were my environment, I'd look to leave a fair/hefty amount of white space in the data file because I like being a disk hog and like to be prepared for future/unexpected growth. So I would be okay giving space back if we just deleted a majority of the space, but I'd never trust those saying "but it will never grow again" and still leave some white space. The route I'd probably go with (sigh) is the shrink approach if I had smaller downtime windows and didn't want to incur the complexity of creating an empty DB and migrating data to it. So I would shrink it a bunch of times incrementally (based on how many times I thought I needed to based on my testing in dev and the desired size. progressively choosing a smaller file size)and then rebuild the indexes.. And then I'd never tell any one that I shrank my database ;-)
DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.We all know that it is not advised to do SHRINK regularly anyway. I try to leave out all the warnings and disclaimers you probably know anyway. Backup, and don't do this at home if possible :)
Bonus: in replication environment if you perform this on the publisher database, it won't cause the subscriber databases to shrank (which may have the size problem because they are Express editions).
Finally, my reindex script:
USE YourDBName
DECLARE @TbName VARCHAR(255)
DECLARE @FullTbName VARCHAR(255)
DECLARE @IxName VARCHAR(255)
DECLARE myCursor CURSOR FOR
SELECT OBJECT_NAME(dmi.object_id) AS TableName,i.name AS IndexName
FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED') dmi
JOIN sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 30
ORDER BY avg_fragmentation_in_percent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TbName, @ixName
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dba' AND TABLE_NAME = @TbName)
BEGIN
SET @FullTbName = 'dba.' + @TbName
IF (@ixName IS NULL)
BEGIN
PRINT 'Reindexing Table ' + @FullTbName
DBCC DBREINDEX(@FullTbName, '', 0)
END
ELSE
BEGIN
PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
DBCC DBREINDEX(@FullTbName, @IxName, 0)
END
END
FETCH NEXT FROM myCursor INTO @TbName, @ixName
END
CLOSE myCursor
DEALLOCATE myCursor
The only variable in this is the 14, which can be obtained by issuing select DB_ID('YourDBName'), and the script assumes that you are interested only in the tables in the dba.* schema.
You have heard all of the warnings about shrinking databases and they are all true. It will fragment your indexes and is in general, muck up your database and shouldn't be done on a production system.
But, I generally do so on a weekly basis when I restore a backup on my workstation due to space on my SSD drive. Mind you, I didn't write this script but found it years ago. On other databases [250 GB], I created an SSIS package that will transfer the tables that I need and then recreate the indexes for that oh so fresh index feel.
DECLARE @DBFileName SYSNAME
DECLARE @TargetFreeMB INT
DECLARE @ShrinkIncrementMB INT
SET @DBFileName = 'Set Name of Database file to shrink'
-- Set Desired file free space in MB after shrink
SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100
SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),
[UsedSpaceMB] = convert(NUMERIC(10, 2),
round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),
[UnusedSpaceMB] = convert(NUMERIC(10, 2),
round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),
[DBFileName] = a.NAME
FROM sysfiles a
DECLARE @sql VARCHAR(8000)
DECLARE @SizeMB INT
DECLARE @UsedMB INT
SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName
SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.
SELECT [StartFileSize] = @SizeMB
,[StartUsedSpace] = @UsedMB
,[DBFileName] = @DBFileName
WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB
BEGIN
SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '
PRINT 'Start ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)
EXEC (@sql)
PRINT 'Done ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)
SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName
SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.
SELECT [FileSize] = @SizeMB
,[UsedSpace] = @UsedMB
,[DBFileName] = @DBFileName
END
SELECT [EndFileSize] = @SizeMB
,[EndUsedSpace] = @UsedMB
,[DBFileName] = @DBFileName
SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))
,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))
,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))
,[DBFileName] = a.NAME
FROM sysfiles a
This quote below is directly from Microsoft (applies to versions 2008-2016), and gives guidance on if/when and how you should use the DBCC SHRINKFILE command.
https://msdn.microsoft.com/en-us/library/ms189493.aspx
Best Practices
Consider the following information when you plan to shrink a file:
- A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
- Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
- A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
- Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.
