Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you be more specific about what you want to do with the information? The concept of database size is fungible (pages allocated for tables, space used in tables, disk size, and so on). –  Gordon Linoff Aug 3 '12 at 13:14
add comment

migrated from stackoverflow.com Aug 3 '12 at 21:19

This question came from our site for professional and enthusiast programmers.

2 Answers

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).

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.