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.

How to Change Database File Size? - Interview Question of the Week #296 ChangeDatabase1-800x424

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

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.

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

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) 

Shrinking Database, SQL Performance, SQL Scripts, SQL Server, SQL Server DBCC
Previous Post
How to Get Volume Mount Point for SQL Server Files? – Interview Question of the Week #295
Next Post
How to Capture Deleted Rows Without Trigger? – Interview Question of the Week #297

Related Posts

Leave a Reply