It’s a really common question: “How do I shrink my database?”
Luckily, there’s also a really common answer: “Don’t.”
…but what if you really have to?
Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.
I’m not even going to talk about log files…that’s a totally different topic. We’re just talking about data files. In this post, I’ll focus on a method that is both online, and works in all editions of SQL Server.
Step 0) Don’t shrink
Before you get to step 1, convince yourself that you really have to shrink your database. I’ll let Brent Ozar (blog|twitter) tell you to Stop Shrinking Your Database Files. Seriously. Now.
If you’re convinced you need to shrink, and you’re not being stupid, then let’s try to do it without causing too much trouble.
Step 1) Look at file size
Before actually thinking about shrinking a file, look at file size and how much free space there is. Run this in the database you want to shrink data files for:
SELECT
LogicalName = dbf.name
,FileType = dbf.type_desc
,FilegroupName = fg.name
,PhysicalFileLocation = dbf.physical_name
,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
- CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
- CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;
If you look at the FreeSpaceMB column in your results, that is the amount of unused space you might potentially be able to reclaim. If it’s significant, you might decide to move on and shrink the files.
Keep in mind that you want set all files for a given filegroup at the same size. When you let file sizes within a filegroup get out of alignment, SQL’s proportional fill algorithm won’t spread IO evenly across files.
Step 2) Consider the side effects
Yes, you’re still trying to talk yourself out of doing this shrink.
If you shrink the data file, any data located at the “end” of the file needs to be related elsewhere in the file. These operations are logged, so you’re going to generate a lot of transaction log usage. Make sure your transaction log is big enough, and make sure you are running transaction log backups frequently enough.
If your database is being mirrored, or is in an Availability Group, or is log shipping, you might find that your secondary server(s) fall behind during this operation.
Shrinking is going to create a lot of fragmentation. Depending on your workload, you might have a performance impact during the process.
Shrinking generates a bunch of IO. If you are already seeing IO issues, that might be exacerbated by shrinking, and you might have performance impact during the process.
Shrinking is a mostly online process, where data is moved page-by-page to the “front” of the file so that the end of the file can be truncated. I say mostly online because its possible for this operation to cause blocking–I’ve seen this particularly often in the PRIMARY filegroup. Because of the possibility of this causing blocking, I never let it run unattended, and never during peak business hours.
Be really sure that you need to shrink.
Step 3) Shrink files
I always use DBCC SHRINKFILE(BOL) when I shrink, never DBCC SHRINKDATABASE. If I am going to shrink a file, I want as much control over the process as possible.
Using the output from the above SELECT, figure out what you want to use for a target size on your database–You’ll want to leave some free space in your data file, otherwise it’s just going to grow bigger right away.
Now you can build the statement(s) to shrink your data file. Remember, this is an online process, but if you’re worried about impacting performance, watch your server carefully while this runs:
USE [DatabaseName]; DBCC SHRINKFILE(LogicalName, TargetSize);
Step 4) Review fragmentation
Congratulations, you’ve successfully created a ton of physical fragmentation. I know some shops run applications where fragmentation just doesn’t cause any problems. If you’re one of those shops, then you’re done–stop reading.
If fragmentation does affect your performance, then you already have a regularly scheduled index maintenance job. After shrinking the data file, run that regular index maintenance job to get things squared away.
If you don’t have a regular index maintenance job, and think you need one: figure out what the best options are, and if you even need to consider it. Kendra Little (blog|twitter) gives a good intro to the topic here. I personally like Ola Hallengren’s IndexOptimize procedure.
But can I do all of this online?
Remember: Even if you’re running Standard Edition, REORGANIZE is an online operation. Better yet, if you kill it you won’t lose your work–it will pick up where it left off the next time.
You can use DBCC SHRINKFILE and REORGANIZE to do this completely online. If the shrink causes massive fragmentation that impacts production, you can shrink/reorganize/shrink/reorganize in smaller chunks. This will take a really, really long time. But that’s a trade-off… There are faster methods, but those will likely involve varying amounts of downtime.
The easiest solution is to just leave the database half-full and accept that you’re using more disk space than you need to. Not shrinking your database at all is always an online operation.
Why are you not just creating another file in the filegroup(s) and moving the data from the old file into the new one using
DBCC SHRINKFILE (N’my_old_file’ , EMPTYFILE)?
After this you can drop the old file or – if you had to create my_new_file on another drive because of disk space – you could shrink my_old_file to e.g. 1 MB (no fragmentation, because it is empty) and use the same command to move the data back to my_old_file and drop my_new_file.
This is certainly an option, too. I’m planning on talking about some of the alternative options in a follow-up post.
Hi Andy,
I concur with your steps completely (especially Step 0) with one minor exception. I found that in Step 3, it is best to shrink in small increments (I had some files with >50GB free). I usually start the increment around 20 or 30MB and depending on performance may work up to maybe 1GB.
Also, You cannot shrink during backups or backup during shrinks.
I was doing this so much for a while that I developed a “nipper” script that sets up for a small shrink, checks for backup in progress, runs the shrink, sleeps for a bit and does it all again. it will stop at a clock time, an elapsed time, or when it reaches Target.
It started from a Power Shell script I found @
http://www.mssqltips.com/sqlservertip/3178/incrementally-shrinking-a-large-sql-server-data-file-using-powershell/?utm_source=dailynewsletter&utm_medium=email&utm_content=text&utm_campaign=20140306
I needed a little more control and tuning so I built the SQL.
Let me know if you want to see the current version. I’ll send it to you.
Ah, at last, the subject I need. As I am just learning SQL-Server this has not come too late. I was just asked to shrink a database. The reason is because it’s a old 2000 version and the Windows server had to be upgraded. Although the Win upgrade to 2008 was successful and the database is still ok, the main reason was that I was supposed to upgrade the SQL server to a higher version and to save on licenses (ohhh, those managers trying to save money!!!) I would have to shrink the database from 21GB to the limit for Std edition which is I believe 10GB. Someone will delete data from 2006 prior to the shrink.
When I started with the standard “idiot” approach I ended expanding the database instead of shrinking it. Also when rebuilding the indexes I was constantly running out of space, obviously the TEMP database on the C: drive wanted to expand which caused the process to fail. So I added another database file on the E: partition (my real database is on D:) and this worked well. However the “reindex” process ended ballooning my database from 20GB to over 40 GB.
As I am normally an Oracle DBA I wanted to recreate the “create index” statements to drop all primary keys and rebuild them, I was also considering creating a separate file group so the statement for the newly created indexes would have the “on ” clause.
This thread gave me a few new options how to actually use a proper approach.
Thanks for all the hints.
Richard
@Richard: Some short points regarding to your post:
– the 10 GB limit belongs to the free SQL Express edition, not to the Standard Edition (which is about 1k USD per core)
– instead of deleting the old data you could have moved them into an archive database (since the 10 GB limit is per database and not per server)
– you should neither the TempDB nor another DB on drive c:, otherwise your Windows system could crash hard when TempDB swallows the last few free KB space
– you could use the SORT_IN_TEMPDB Parameter in the index create / rebuild statement to prevent its from blowing up your db files (ideally your TempDB is placed on a SSD or RAID 10 SAN, so this would be faster too).
– The size of TempDB should be at least as the biggest table in your DB (better double its size)
Hi Thomas,
I value your hints very much, as before I do anything I need all the info to do things correctly (or not to do at all).
Correct me if I am wrong :
– “the 10 GB limit belongs to the free SQL Express edition” – this relates to the database size limit. Express edition is free of charge. Correct?
(let’s leave the details of licensing for another time, can’t argue with my bosses…don’t know what they are up to and I can only recommend)
– as for archiving – this is a third party database and it is oursourced. I want to recommend this to my manager : “if you want to reduce the about of data this needs to be a regular process, otherwise there is no point in shrinking/reorganizing. Reindexing possibly yes, makes sense after removing a huge amount of data.” . I base all my work on such assumptions. This database does not have a regular Delete/Clean process defined and I cannot force the designer to implement such a change at this time. Whether I can have this implemented is not up to me, so my options are limited. I will not be doing the Delete process, as I do not have the knowledge of the structure, nor is it documented.
– TempDB not on C: – I totally agree, but we would have to tell this to those who invite outsourcing companies to install their databases without using the C: drive. From what I can see the only database file on the D: drive is the data file only, all the rest are on C:. We can only blame the implementor when this was installed without proper DBA oversight. The only thing I am testing is adding another TempDB file on the D: drive and disabling autogrow of the file on the C: drive. This I think I am allowed to change.
All other hints, thanks very much, this really helps.
Richard
Everything correct.
Regarding the 3rd party solution:
I could not find a sheet, that tells us, if the new SQL 2016 strech DB feature is in the free SQL Express edition (and if it could be used to bypass the 10 GB database size limit). If yes, it could be a nice way to handle 3rd party databases (on the other hand you still need to know where / how the data is stored).
On the other hand: when your vendor will delete the old data, it should be self-evident that he not just run a DELETE but archives the data in some way (= other DB) before (everything else would be a very bad practice).
Regarding TempDB:
if you are able to add another TempDB-File to d: you could also remove the TempDB-File from c:. Furthermore you should add some more TempDB-Files (Number of CPUs but not more then 8 files, all with exact the same size and grow ration (per x MB not per percent)). Please be aware, that you’ll need to restart the SQL Service after doing this (otherwise it would not use them)