Yadtrt
asked on
Intial Data file size
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.
What is the better Intial Data file size and growth of my situation to give better performance.
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].[Database Growth]
¿¿¿¿(
¿¿¿¿¿¿¿¿ [Database_Name] [char](128) NOT NULL
¿¿¿¿¿¿¿¿,[Database_Size_MB ] DECIMAL(15,2) NOT NULL
¿¿¿¿¿¿¿¿,[Unallocated_Spac e_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.DatabaseGro wth
([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,2 15,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].[Database 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_
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿)
BEGIN
¿¿¿¿CREATE TABLE DBAControl.[dbo].[Database
¿¿¿¿(
¿¿¿¿¿¿¿¿ [Database_Name] [char](128) NOT NULL
¿¿¿¿¿¿¿¿,[Database_Size_MB
¿¿¿¿¿¿¿¿,[Unallocated_Spac
¿¿¿¿¿¿¿¿,[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.DatabaseGro
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb¿¿¿¿
'DECLARE
¿¿¿¿@pages¿¿¿¿BIGINT¿¿¿¿¿¿
¿¿¿¿,@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,2
¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿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
/*Show data*/
SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBAControl.[dbo].[Database
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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