How to Change Database File Size? – Interview Question of the Week #296

Question: How to Change Database File Size?

Answer: By Shrinking the files. Now, many have a negative opinion about the Shrinkfile but the question is how to change the file size and the answer of the same is by Shrinking the file.

Here are the primary reasons why the Shrinking database file or a Shrinking database is not good.

• SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
• SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
• SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation
• SQL SERVER – Killing DBCC SHRINKFILE Process – Is it Safe?

Now let us get back to the original question. How to change the database file size. Let us go to SQL Server Database Properties and go to the files tab. Under here, change any file size and click on the Script on the top.

Once you click on the Script, you will see it generating the script something like this.

USE [SQLAuthority]
GO
DBCC SHRINKFILE (N'SQLAuthority' , 200)
GO

That’s it. So if you want to change the file size you will have to Shrink the file. While I am not a big fan of Shrinking the file at any day as it negatively impacts the performance, I think it is the solution if you are changing the file size. You can always connect with me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)