Solved

Intial Data file size

Posted on 2011-02-25
3
409 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now