0

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.

Improve this question
3
  • 1
    It is not a generic SQL question. Answer can be different based on which SQL flavour you're using. Please tag your question with DB engine of your choice. – Justinas Marozas Jan 18 '18 at 10:02
  • There is an .mdf file mentioned. That means, there is an MS SQL Server involved. – rollstuhlfahrer Jan 18 '18 at 10:04
  • DBBC SHRINKFILE takes a target size parameter: docs.microsoft.com/en-us/sql/t-sql/database-console-commands/… – Allan S. Hansen Jan 18 '18 at 13:40
0

You can shrink you database file using SQL Server Management Studio.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases and then right-click the database that you want to shrink.

  3. Point to Tasks, point to Shrink, and then click Files.

Source: https://docs.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

Improve this answer
2
  • Thanks a lot. But my question was if I shrink, will it shrink all the way down to some 20+ GB even if I have initialized DB of size 120 in the beginning? – Some_user Jan 18 '18 at 10:43
  • Using this method, if you specify the size you want to shrink to, you can shrink below the size set when the DB was created. If you don't specify the size, then the minimum size the DB can shrink to is the size specified when it was created (which, in the OP's case, sounds like 120 GB). – RDFozz Jan 18 '18 at 21:40
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.