Solved

Intial Data file size

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

837 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