April 20, 2016 at 7:05 am
Greetings all;
Not sure if anyone else has experienced this but this past March I moved a tier 1 database from SS2K8R2 to SS2K14. All has appeared to be working properly with the regard to overall performance. The one thing I have witnessed is the rate of growth of the database over the past 1 1/2 months. Growth rate is increasing at a more rapid rate than when on the old DB platform.
In terms of maintenance plans, nothing has changed. In terms of any operational data changes, again nothing has changed.
Has anyone else experienced a similar issue?
At this point I'm at a loss as to where to look. Any ideas?
Thanks for taking the time to read this.
Your comments are much appreciated.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
April 20, 2016 at 2:00 pm
OK so my initial post may have been somewhat inaccurate. What I did was put a report showing at what times the data file grew. I saw several periods during the day/evening where I saw the data file was growing continuously for several minutes. I was then able to correlate this with a series of ETL jobs running during the same time periods. It appears to be several ETLs that are the culprits.
So much for saying nothing has changed. It appears that either 1 or more of these ETLs changed in return changed the dynamics of the daily load process.
My next step is to speak with the Applications people to see what might have changed along the way causing a data growth spurt.
More later...
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
April 20, 2016 at 2:05 pm
I would also look at the current size of your database, the amount of free space in the database, the automatic growth factor set for the database.
You should not rely on the auto growth for increasing the space needed for your database. You should determine the amount of free space needed for three to six months growth in data. Establish an alert when free space starts getting low so that you can grow the database at a time when doing so will have less impact on the system and application.
April 22, 2016 at 11:52 am
A bit more background.... We are self-hosting a vendor supplied application so I didn't think about changing with the physical database attributes. Additionally because the vendor is rather tight with their code I can't really go in and make any assessments on their code and why it is bloating the database the way it does.
The best I can do is reach out to the vendors technical team to inform them of what is going on to see if they can remedy the issues going forward. What appears to be odd is the fact that this process that is bloating the database has been in place since we went live with this system. I have to see if someone can point me to a change request that would have changed the dynamics of this process.
Thanks for the heads up on pre-allocating the database size.
Now on a related point, on the auto growth feature of SQL Server, my first impression is that the database will grow during a load process, which would make sense. I have found situations where it appeared the database was somewhat quiet and found it to grow. Could that be possible?
Thanks again.
Kurt
Lynn Pettis (4/20/2016)
I would also look at the current size of your database, the amount of free space in the database, the automatic growth factor set for the database.You should not rely on the auto growth for increasing the space needed for your database. You should determine the amount of free space needed for three to six months growth in data. Establish an alert when free space starts getting low so that you can grow the database at a time when doing so will have less impact on the system and application.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply