I have a question about SQL Server databases.
I have initialized a database (let's say Configuration_DB) of size 120GB following with certain application installation. This application installation has taken up 20GB out of that 120GB DB space and 100 GB is left free.
Now if I run shrink job on that, will it be reduced to 20Gb (plus something) or how much it can be reduced to, although the Configuration_DB.mdf file has already taken 120 GB on disk.
Thanks.
You can shrink you database file using SQL Server Management Studio.
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases and then right-click the database that you want to shrink.
Point to Tasks, point to Shrink, and then click Files.
Source: https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file
Please be advised, that the databse needs at least some free space to work. So don't shrink it all the way down to 20 GiB
Yes, you can shrink it to (close to) 20GB, but I wouldn't recommend it.
Instead, pick reasonable target sizes, with sufficient free space, for the data and log file individually. Give SQL some "elbow room", if you will.
In your case, I'd suggest, say, a 25GB data file (20% free space), and also probably a 5GB log file (which could vary depending on expected activity, and whether you are using simple or full recovery mode).
@rollstuhlfahrer's answer about how to do that is correct: use the "Shrink File" feature so you can be specific about your target size, not "Shrink database".
WARNING: Shrinking should never be routine or scheduled, it should be a rare event in response to a specific need (like in your case, to recover an initial over-allocation).
WARNING 2: Shrinking tends to fragment tables, I'd recommend doing a reindex after your shrink operation, which may reclaim some of the space you've just freed. If it does, just leave it at that size.
