January 18, 2018 at 7:06 am
Hi all,
I have a question why and how it can be concluded that the initial size should be change from default and assign a big space as per DB expected size ?
example:-one of my DB which initial size is 300 GB but the data it contain is only 30 GB so what is the need of the initial size giving 300 GB ?
Thanks
Ivan
January 18, 2018 at 7:18 am
Ivan
If you know, or suspect, that the database will eventually grow to 300GB, you may as well start it off at that size. That's preferable to risking physical fragmentation and temporarily impaired performance when it does eventually grow.
John
January 18, 2018 at 7:19 am
Ivan Mohapatra - Thursday, January 18, 2018 7:06 AMHi all,I have a question why and how it can be concluded that the initial size should be change from default and assign a big space as per DB expected size ?
example:-one of my DB which initial size is 300 GB but the data it contain is only 30 GB so what is the need of the initial size giving 300 GB ?Thanks
Ivan
In general, growing the file size is an expensive operation - you want the growth to happen as infrequently as possible.
Sue
January 18, 2018 at 9:08 am
Maybe an analogy to think about is have you ever been at the store when the register is out of receipt paper? The whole line stands there and waits while the person at the register fumbles around with taking out the old empty spool, tries to find a new spool of paper, and then feeds it into the register to get it started. While it may seem a silly comparison, if some processes are trying to insert data into a data file of your database, and there are no more extents available in the filegroup to grow the table that is being inserted into, then all those processes have to wait, and any reads that require pages still on disk will also be waiting while the OS fumbles around adding more disk space to the filegroup data file(s). While it may not be possible for the cashier at the store to always have a paper spool large enough for their entire shift, if we can estimate how much data will be inserted into a database for a period of time, why not plan for that size to begin with?
January 18, 2018 at 9:15 am
It also comes down to the DBA controlling when the database is grown instead of relying on auto-growth to handle expanding the databases. This is one of the reasons production DBA's monitor the space available in their databases. Even though you monitor the space there may be an extraordinary circumstance that occurs that requires the database automatically grow to meet the existing need for space. This shouldn't be the normal state of affairs, even though it seems to be in many environments.
January 18, 2018 at 8:53 pm
Hi all thanks for ur all respond but what is bothering me is the space that has been assigned 300 gb and the data is not using that 300 gb it has grown to 330 gb as of now so what is the use of that 300 gb if my data file is not able to use it ?
Does you guys have any authentic information from msdn ?
January 18, 2018 at 9:06 pm
So you are saying that 300gb that is allocated upfront is not getting used at all?
Thanks!!
January 19, 2018 at 2:21 am
So it's grown to 330GB but the original 300GB isn't being used? I don't really understand... unless this database is used as a staging area where large amounts of data are loaded and then removed soon afterwards?
By the way, it sounds as if your autogrowth factor is 10%. I would recommend you change this to an actual number - maybe 20GB, depending on how you expect the database to grow, whether you have Instant File Initialization enabled, and how fast your storage is.
John
January 19, 2018 at 6:13 am
Lets try to clarify what's going on. Can you run the following queries in your database?
show file sizes and used space:SELECT DB_NAME() AS database_name,
f.file_id, f.name AS file_name, f.type_desc,
LEFT(f.physical_name,1) AS drive, f.size/128 AS size_MB,
FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
f.is_percent_growth
FROM sys.database_files f
show items used space:SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.index_id, i.name AS index_name,
SUM(in_row_used_page_count) * 8 AS rowused_KB,
SUM(lob_used_page_count) * 8 AS LOBused_KB,
SUM(row_overflow_used_page_count) * 8 AS overflow_KB,
SUM(used_page_count) * 8 AS total_KB
FROM sys.all_objects t--sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE t.type IN ('S','U')
GROUP BY t.schema_id, t.name, i.index_id, i.name
ORDER BY total_KB desc, 1, t.name
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply