SQL Server specifies that the initial database size cannot be shrunk when the database is created

When you create a database in SQL Server you can specify the initial size of the database file, for example, we set the size of the newly created database mydb to 1024MB

Well, the database you built will actually take up about 1024MB of disk space.

However, using the stored procedure sp_spaceused you can see that there is 1021.85 MB of space that is mydb occupied unused.

So we were wondering if we could use DBCC SHRINKDATABASE or DBCC SHRINKFILE statements to shrink unused database space

DBCC shrinkdatabase ('MyDB',ten)

But after executing the shrink statement, we found that the database size did not change the slightest ...

And be prompted after executing the Shrink statement

This means that SQL Server thinks mydb has no space to contract ... But in fact, we can see through the stored procedure sp_spaceused that mydb really has 1021.85 MB of space that is unused. The reason why the database cannot be shrunk is that the initial size specified when creating the SQL Server database cannot be shrunk, and the unused space can be shrunk by using the ALTER DATABASE statement or by re-specifying the initial size of mydb in SMSS.

Now we'll change the initial size of MyDB to 800MB in SMSs.

You can see that the size of the mydb is really around 800MB now.

And now we're going to change the initial size of mydb back to 1024MB.

Then execute DBCC SHRINKDATABASE (' MyDB ', 10), you can see that the database size becomes about 800MB, so in fact shrinkdatabase statement can only shrink the database to The smallest initial file size that has been specified since the start of the database , if your database was first created with a size of 1024MB, and no one has ever been transferred to a size so sorry, the smallest of your data can only shrink to 1024MB. Although it is possible that 1023MB is not actually used ...

SQL Server specifies that the initial database size cannot be shrunk when the database is created