Sql-server – How SQL server database reorganize memory

sql serversql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – Does SQL Server use pointers instead of storing duplicate rows

No, SQL Server does not detect duplicate rows

SQL Server is filling up empty or partially empty pages within the allocated pages.

So if I have a very narrow row (2 columns say), I can add a few hundred more rows on the same page without increasing space used.

Quick and dirty demo (without duplicate rows, but you can play with this if you want)

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO
CREATE TABLE dbo.Demo (DemoID int NOT NULL IDENTITY(1,1), Demo char(1) NOT NULL)
GO
SELECT 'zero rows, zero space', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('a');
GO
SELECT 'one row. Peanuts', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 100
SELECT '101 rows. All on one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 1899
SELECT '2000 rows. More than one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

TRUNCATE TABLE dbo.Demo
GO
SELECT 'zero rows, zero space. TRUNCATE deallocates pages', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('c');
GO 500
SELECT '500 rows. Some space used', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

DELETE dbo.Demo
GO
SELECT 'zero rows after delete. Space still allocated', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO

SQL Server – How to Shrink a Log File Multiple Times

Next time, run two checkpoints before trying to shrink the file (assuming simple recovery) or take two log backups (assuming full or bulk-logged). This will force the log to wrap around to the beginning of the file, allowing the shrinkfile operation to eradicate most of the file. I think the fact that the 4th operation was successful was just by coincidence (perhaps multiple checkpoints happened in the meantime).

That said, why on earth would you want a 1 MB log file? If it grew once, it will grow again. Freeing up the space temporarily just so that it can autogrow during real activity (which blocks, because the growth of the log has to be zero-initialized, even with instant file initialization) seems like an exercise in futility. Why not just size the log appropriately, so that you don't have to deal with growths (ideally) or shrinks (wasteful).

Please have a good thorough read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Further reading that might be more useful than trying to summarize:

Best Answer

Related Solutions

Sql-server – Does SQL Server use pointers instead of storing duplicate rows

SQL Server – How to Shrink a Log File Multiple Times

Related Question