Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Intial Data file size

Posted on 2011-02-25
3
Medium Priority
?
417 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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

670 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