Solved

Intial Data file size

Posted on 2011-02-25
3
413 Views
Last Modified: 2012-06-22
I have a databae used for web application, it has few daily transaction 50 per day, but it has traffic load on retriving data (selecting data).
What is the better Intial Data file size and growth of my situation to give better performance.
0
Comment
Question by:Yadtrt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Expert Comment

by:slam69
ID: 34980445
If you can set the initial size to be sufficient to allow for internal growth. if you set the file big enough to support your long term use it means it will be kept as one frgament on the disk and therefore will stand less chance of fragmenting as it has to grow in an auto growth set up.

make sure you have autoshrink DISABLED really shoudlnt shrink DB unless specifically required again as this will cause fragmentation.

remember whenever the file auto grows it will do this whenever it reaches specified trheshold, if thsi is at yoru busy times it will cause the db to lock whilst it grows.

give yourself plenty of space, set autogrow on in case it reaches a limit and let it autogrow by a fixed amount of space, 50mb shoudl be sufficient from teh sounds of your load. however monitor the free space and if it reaches close to the threshold consider growing it manually out of hours to minimise the impact iof autogrowth
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34986190
hi use this script to gather information about you database sizes and growth.

create a database and load the data into it as it was your prod env
track the changes for a while and then you will have the exact number for you.


/*
Create a table to store results.
*/
IF NOT EXISTS (SELECT 1
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿FROM DBAControl.sys.tables
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHERE Name = 'DatabaseGrowth'
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿)
BEGIN
¿¿¿¿CREATE TABLE DBAControl.[dbo].[DatabaseGrowth]
¿¿¿¿(
¿¿¿¿¿¿¿¿ [Database_Name] [char](128) NOT NULL
¿¿¿¿¿¿¿¿,[Database_Size_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Reserved_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Data_MB] BIGINT NOT NULL
¿¿¿¿¿¿¿¿,[Index_Size_MB] BIGINT NULL
¿¿¿¿¿¿¿¿,[Unused_MB] BIGINT NOT NULL
¿¿¿¿¿¿¿¿,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE()
¿¿¿¿) ON [PRIMARY]
¿¿¿¿WITH (DATA_COMPRESSION = PAGE
END

/*
Insert the data into storage table
*/
INSERT INTO DBAControl.dbo.DatabaseGrowth
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb¿¿¿¿
'DECLARE
¿¿¿¿@pages¿¿¿¿BIGINT¿¿¿¿¿¿¿¿¿¿¿¿-- Working variable for size calc.
¿¿¿¿,@dbname SYSNAME
¿¿¿¿,@dbsize BIGINT
¿¿¿¿,@logsize BIGINT
¿¿¿¿,@reservedpages BIGINT
¿¿¿¿,@usedpages BIGINT
¿¿¿¿,@rowCount BIGINT

SET NOCOUNT ON

¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ @dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
¿¿¿¿¿¿¿¿,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
¿¿¿¿¿¿¿¿FROM [?].dbo.sysfiles

¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ @reservedpages = SUM(a.total_pages)
¿¿¿¿¿¿¿¿,@usedpages = SUM(a.used_pages)
¿¿¿¿¿¿¿¿,@pages = SUM(
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿CASE
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿/* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN a.type <> 1 THEN a.used_pages
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN p.index_id < 2 THEN a.data_pages
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿ELSE 0
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿END
¿¿¿¿¿¿¿¿¿¿¿¿)
¿¿¿¿FROM [?].sys.partitions p
¿¿¿¿JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
¿¿¿¿LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id

¿¿¿¿/* unallocated space could not be negative */
¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ database_name = ''?''
¿¿¿¿¿¿¿¿,database_size = (@dbsize + @logsize) * 8192 / 1048576
¿¿¿¿¿¿¿¿,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
¿¿¿¿¿¿¿¿,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'

/*Show data*/

SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBAControl.[dbo].[DatabaseGrowth]


0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 34986196
the script again with no ??? marks


/*
Create a table to store results.
*/
IF NOT EXISTS (SELECT 1
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿FROM DBAControl.sys.tables
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHERE Name = 'DatabaseGrowth'
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿)
BEGIN
¿¿¿¿CREATE TABLE DBAControl.[dbo].[DatabaseGrowth]
¿¿¿¿(
¿¿¿¿¿¿¿¿ [Database_Name] [char](128) NOT NULL
¿¿¿¿¿¿¿¿,[Database_Size_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Reserved_MB] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Data_MB] BIGINT NOT NULL
¿¿¿¿¿¿¿¿,[Index_Size_MB] BIGINT NULL
¿¿¿¿¿¿¿¿,[Unused_MB] BIGINT NOT NULL
¿¿¿¿¿¿¿¿,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE()
¿¿¿¿) ON [PRIMARY]
¿¿¿¿WITH (DATA_COMPRESSION = PAGE
END

/*
Insert the data into storage table
*/
INSERT INTO DBAControl.dbo.DatabaseGrowth
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb¿¿¿¿
'DECLARE
¿¿¿¿@pages¿¿¿¿BIGINT¿¿¿¿¿¿¿¿¿¿¿¿-- Working variable for size calc.
¿¿¿¿,@dbname SYSNAME
¿¿¿¿,@dbsize BIGINT
¿¿¿¿,@logsize BIGINT
¿¿¿¿,@reservedpages BIGINT
¿¿¿¿,@usedpages BIGINT
¿¿¿¿,@rowCount BIGINT

SET NOCOUNT ON

¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ @dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
¿¿¿¿¿¿¿¿,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
¿¿¿¿¿¿¿¿FROM [?].dbo.sysfiles

¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ @reservedpages = SUM(a.total_pages)
¿¿¿¿¿¿¿¿,@usedpages = SUM(a.used_pages)
¿¿¿¿¿¿¿¿,@pages = SUM(
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿CASE
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿/* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN a.type <> 1 THEN a.used_pages
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿WHEN p.index_id < 2 THEN a.data_pages
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿ELSE 0
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿END
¿¿¿¿¿¿¿¿¿¿¿¿)
¿¿¿¿FROM [?].sys.partitions p
¿¿¿¿JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
¿¿¿¿LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id

¿¿¿¿/* unallocated space could not be negative */
¿¿¿¿SELECT
¿¿¿¿¿¿¿¿ database_name = ''?''
¿¿¿¿¿¿¿¿,database_size = (@dbsize + @logsize) * 8192 / 1048576
¿¿¿¿¿¿¿¿,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
¿¿¿¿¿¿¿¿,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
¿¿¿¿¿¿¿¿,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'

/*Show data*/

SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBAControl.[dbo].[DatabaseGrowth]


0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question