Shrinking and Growing Databases
The advantage of using a fixed database size is that you minimise file fragmentation.
The disadvantage of using a fixed database size is that you might run into problems if the database grows in size over time and the space allocated to the database is filled.
Increasing Database Size
By default, database files automatically grow as they approach capacity.
ALTER DATABASE [AdventureWorks2012] MODIFY FILE ( NAME= N'AdventureWorks2012_Data', SIZE = 256000KB ) GO
Decreasing Database Size
When a database is configured to autoshrink, Database Engine automatically shrinks databases that have free space.
Autoshrink will cause indexes to become fragmented. If you remove a lot of data from a database and want to reclaim space, it is often better from a performance perspective to shrink the database manually and then rebuild the indexes than it is to leave autoshrink on.
If you originally created a database that was 500 MB in size and it grew to 1000 MB, you cannot shrink the database to smaller than 500 MB even if you delete all data in the database.