unexpected data gorwth?

Question

Post reply

unexpected data gorwth?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 10, 2014 at 5:39 am

#306232

Hi,
Database growth increase 1GB on daily basis.
Data File Size - 632 GB
Log File Size - 3.5 GB
As per current configuration and data growth setting in database
Data File
---------
1. unrestriced growth
2. File growth - 5 MB.
Log File
---------
1. unrestriced growth
2. File growth - 10%.
1. rebuild index operation schedeuld on weekly basis
I am using Ola.hallgern maintenance script for defragmentation of Index, it tooks just 23 mints
This maintenance not more than increase 1 GB data growth.
Colud you suggestion me.what are the other points need to check for fixing data growth.
Thanks
ananda

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

I would put the file growth of your data file a bit higher than 5MB.

Say you reach the limit of the database file but it doesn't have to grow yet.

The next day you want to add 1GB of data. If you grow per 5MB, the data file has to grow 200 times to be able to include the new data. This will slow down the data load. And this will happen every day after the limit has been reached.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

First find out from where data is comming? Depend on the application and design it could be possible to grow data 1GB/DAY. Also find out what kind of data is inserting which will help you to define purge or archive policy. Purge or archive might help you to keep check on your data growth. Also if that is the case you need to plan for disk space required in near future. Does your backup too increased by 1 GB?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

This database there is no data retention period also management not accepted to delete old data for identified block listing people.

Data format is storing binary and encrypt format for Photo identification details. Only one Image table have occupied 530 GB.

Already configured on each table for capture data growth on daily basis with history data. In that table size also increased 1GB on daily basis.

Can you suggestion me? Data growth setting can be changed to GB size, so that data file to avoid the growth increment.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

yes. backup file also increased some GB size.

Daily incremental backup. and monthly full backup due to disk free sapce capacity.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

Already configured on each table for capture data growth on daily basis with history data.

verifired the history table that Image table row size has increased 1GB on daily basis.

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

If your datagrowth is 1 GB/Day; I suggest to set FILE GROWTH by 5/10 GB. Also think about storage too.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 7

I resized data file gorwth setting 5 GB then after 5 hrs data files grown upto 642 GB.

free_mascot One Orange Chip Points: 27169 More actions · Answer 8

Make sure you enable Instant Initialization for filegrowth and than set filegrowth to 10 GB.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

Performance vloume task, it has been done at initial setup when database was upgrade to 2008 R2 from 2000.