Have you been in a situation where you wanted to make a backup of a database and restore to another server and realized you couldn’t backup and restore to the new location because of the size of the database? How about even after dropping or truncating huge tables and still the database size is not getting smaller for you to restore to the new location? This is a common challenge for anyone working with many databases and data in their day to day activities and l want to write a comprehensive guide to reduce the database size after removing unwanted data. I am assuming you have removed any unnecessary data but the size of the database is not getting smaller. The cause of this issue is behind the scene of the working of SQL server engine. Even though you have removed all that data or the data in the database does not match the size of the database being reported simply because SQL server engine has not released the previously occupied space to the operating system so its like a bubble of space which will be filled again when data is inserted into the database. We are going to learn how to get the size down to the actual size of data and get ride of the bubble and let the operating system reclaim that space. I am going to use AdventureworksDW2016CTP3 as my database of reference to carry out the steps.
Step 1: Check to see if there are spaces in the database files that you can reclaim. Using SSMS right click the database, go to tasks, go to shrink and choose files. Below snapshot is what you should see, in my case for AdventureworksDW2016CTP3.
In the snapshot above you can see the allocated space and the available free space for the file type of Data. The available free space is the unused space out of the allocated space. It tells you what percent of the allocated space is unused. So if you had truncated or dropped some tables and there are bubbles in the database files, this will tell you how much of that space you can get back when you shrink the file. This estimate guides you to know if you have already removed enough data to allow you to restore the database to the new location with limited size.Note we chose the shrink option for files so we have the option to shrink either the data file or the log file separately. You could also choose the shrink option as database which will shrink the entire database. In our example we are going to shrink the data file. From the above snapshot, the shrink action has options release unused space, reorganize pages before releasing unused space, empty file by migrating the data to other files in same file group. The second option to reorganize pages before releasing unused space is more costly but produces the most efficient way to get back the unused space. This option allows you to shrink the file to a specific size which of course must be greater or equal the minimum space required to store the current data in the database. In addition, it rearranges the data pages in organized form so reading data from the disk will be more continuous rather than jumping from one place to the other to retrieve data.
Step 2: Shrink the data file to the desired size. One important note is the shrink operation is resource intensive and can run for hours if you are trying to get back lot of space. Also, it is good to know you can do the shrinking little bit at a time. For example, assuming you have 300GB of unused space in your data file and you are trying to reclaim all of it. This could take you more then 6 hours to complete but you can also shrink the file incrementally that is to say you can get back the unused spaces some chunks at a time.Assume in the snapshot above, we have 300GB of allocated space and 250GB of available free space. Which means our current data needs approximately 50GB to store in the database so we can get back 250GB. In this case we can easily shrink the data file to 50GB but before you start note that shrink operation will take long to complete say hours so you might want to shrink it to maybe 200GB first and if that still can’t fit in your new location then shrink it further. I normally prefer running scripts to accomplish this type of tasks so lets get to shrinking using sql scripts. First I am going to check the files and how much space is available with below query.
SELECT file_id,name, type_desc, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
I am now going to change the recovery mode for the database to simple and note the data file is AdventureWorksDW2014_Data and it has file_id of 1, the log file is AdventureWorksDW2014_Log and has file_id of 2 from the output above.Below is the query for the shrink operation.
ALTER DATABASE AdventureworksDW2016CTP3
SET RECOVERY SIMPLE;
GO
-- Shrink the data file to 1000 MB. this is assumption.
DBCC SHRINKFILE (AdventureWorksDW2014_Data, 1000);
GO
-- Shrink the log file to 1 MB. This is assumption
DBCC SHRINKFILE (AdventureWorksDW2014_Log,1);
GO
You can check the status of the shrink operation with below operation. This gives you the estimated time of completion of the process which will be very helpful if you are reclaiming lot of space. My advice is to create a job to run the shrink operation.
SELECT R.STATUS, R.COMMAND, DATABASENAME = DB_NAME(R.DATABASE_ID),R.START_TIME,
ESTIMATED_COMPLETION_TIME_In_Hours=(R.ESTIMATED_COMPLETION_TIME/1000)/(60*60),
CPU_TIME_In_Minutes=(R.CPU_TIME/1000)/(60),
TOTAL_ELAPSED_TIME_In_Minutes=(R.TOTAL_ELAPSED_TIME/1000)/(60),R.PERCENT_COMPLETE
FROM SYS.DM_EXEC_REQUESTS R
where R.command ='DbccFilesCompact'
SELECT * FROM sys.dm_exec_requests WHERE command ='DbccFilesCompact'
After you have finished shrinking the files, you have to release the space to the operating system with the truncateonly option.
After that change the recovery back to the original. Below query will accomplish that task.
-- release empty space to operating system from data file, file_id = 1
DBCC SHRINKFILE (1, TRUNCATEONLY);
GO
-- release empty space to operating sytem from log file, file_id = 2
DBCC SHRINKFILE (2, TRUNCATEONLY);
GO
ALTER DATABASE AdventureworksDW2016CTP3
SET RECOVERY Full;
I hope this topic is useful. Any suggestion is welcome.
