How SQL server database reorganize memory

Question

I am using [sys].[master_files] table to determine the size of the database, however i didn't get a sensible value yet. If i am inserting 100 rows into a table ideally db size should increase while i am getting the same size even after inserting 1000 rows , sometimes it may decrease unexpectedly. Then i drop all the tables from the database and after that it shows 188 MB from 180 , size increased after dropping all tables. This is also not good.

When i am using DBCC SHINKDATABASE ('dbName') , i am getting a good value. It reduced to 22 MB. My question is how i can determine the size of a particular DB , which is showing sensible changes. Any help will be appreciated , thanks.

Answer 1

SQL Server does its own database file management - it does grow your .mdf file every time you need a few bytes more space.

Instead, for each database file, you've defined an initial size and how it will grow - by percent or by a fixed size. You can see these values by issuing this query:

SELECT 
    name, physical_name,
    size, max_size, growth, is_percent_growth
FROM sys.database_files

If your database file is set to e.g. 180 MB in size, and the growth is set to 50 MB, then as soon as the space in the allocated 180 MB is no longer sufficient, the file will be grown by 50 MB (to 230 MB).

By default, SQL Server also never shrinks data files - what for? Just to grow them again later on? It keeps track of unused space inside the file, and will reuse that space, until again, the file size isn't sufficient anymore, at which point it will be grown again (by that settings that's defined per file).

Answer 2

Try using sp_spaceused, which shows unused/unallocated space.

The size does not reduce, as the space is still allocated to the database file. A shrink will release the space claimed earlier. If the database at the time you create the rows, has enough space available to cater to these rows, it thus also does not actually appear to show any growth, as no additional space needed to be allocated.