June 1, 2009 at 3:09 pm
I have 2 databases with different sizes;
Database A size 6GB
Database B size 18GB
I have to merge both databases into database C. (new database)
note: my database A doesnt growth , my database B growth 2% everyday. my environment is OLTP..
What I did, i created database C with 30GB and logfile of the 25% of the database size 'cause Im using OLTP..
What is the correct initial zise of the .mdf and .lgf of the new database C? am I doing the correct calculation?
Regards,
June 1, 2009 at 3:29 pm
18 Gig with 2% growth per day will pass the 24 Gig mark in just about 2 weeks. It'll be over 600 Gig in 6 months. Assuming compound growth.
Assuming you mean it'll grow by 360 Meg per day (2% of 18), then it'll exceed 24 Gig in just about 2 1/2 weeks, and will be over 80 Gig in 180 days (about 6 months).
So, if you want to plan for 6 months of growth, you'll need to make sure it's at least that size. Six months is usually a good span for that kind of planning.
Not sure where you got the 25% of MDF for LDF. That'll depend on transaction volume, recoverability, and backup plans. If the database is in Simple recovery, you have much less to worry about on LDF size and growth. If it's got log backups running every 5 minutes, you'll have different size and growth needs than if it has them once per day.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2009 at 3:53 pm
You are right.. I have a Simple Recovery Model and my logs backup every hour.. Im confuse because I got this information in a Microsoft Training. In this case, what is the correct size for a log file??
About the database, the minimum u mention will be 80GB? If so, what happen after the 6th month? The Database C has "By 1 MB, unrestricted growth "
i jsut want to create the right initial size of the Database C.. I jsut have 2 months in working with this information and its not pass reference about the growth. This is what I saw in the last 2 months only.
Regards,
June 1, 2009 at 9:24 pm
Hi,
Sorry but your last statement " DATABASE IS IN SIMPLE RECOVERY MODEL" & "LOG FILE BACKUP IS DONE EVERY HOUR", how this is possible? In Simple recovery model we cannot take backup of Log File (Unless its Physical where we stop SQL and copy it whcih I am sure we are not).
Regards
GURSETHI
June 3, 2009 at 7:16 am
MTY (6/1/2009)
You are right.. I have a Simple Recovery Model and my logs backup every hour.. Im confuse because I got this information in a Microsoft Training. In this case, what is the correct size for a log file??
The correct size for the log file isn't a formula or rule. It's going to be different for different databases. What you need to do is make your best guess, and then review it periodically and make changes based on what you observe.
When I create a new database, if it's going to be transactional, I usually set the intial log size to 1 Gig, and set 1 Gig auto-grow on it, and then keep an eye on it for a while. I'm more likely to adjust the frequency of log backups than to increase the size of the log file, once I get data on what it needs.
As already mentioned, you can't have a database in simple recovery and do log backups. SQL Server doesn't do that. Take another look at it, you may have accidentally misread something in the settings or in the backup code.
About the database, the minimum u mention will be 80GB? If so, what happen after the 6th month? The Database C has "By 1 MB, unrestricted growth "
Ideally, you don't wait for six months and then handle it, you monitor how the data is growing in the tables, and you take action before you run out of space.
1 MB unresistricted growth is almost certainly going to result in massive disk fragmentation, and that can have a negative impact on the performance of the whole system. It's much better to set that to a higher number, and then to grow the database manually under your control, rather than let it autogrow.
i jsut want to create the right initial size of the Database C.. I jsut have 2 months in working with this information and its not pass reference about the growth. This is what I saw in the last 2 months only.
Based on the growth over the last two months, estimate how big it will be in another two months. Give it a bit more room for that, and then check on it every week and see how it's doing. If you log the data growth (in a database or even just in Excel), you'll soon see whether you picked the right size or not.
What you want to have happen is: Every six months to a year, you want to allocate more space to it, and review things like "do we need larger hard drives". In between, you want it to just keep running along. The way to achieve that is to estimate the growth for that period, and make sure it's big enough for that. And then set an autogrow increment that's big enough to handle emergencies, but small enough to not try to eat the whole disk.
If you don't know how big it's going to be, and don't have any way to work it out, pick something arbitrary and then just keep a close eye on it. Pretty soon, you'll have enough data to make a better decision.
A LOT of a DBA's job is "keep an eye on it and work out how to prevent problems based on what you observe". The cycle is, "observe, analyze, prevent".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2009 at 7:55 am
Hi,
Im sorry for the delay. I just want to comment that I was looking at development database when I mentioned the simple recovery model... My production database is on Full Recovery Model. Full, Differencials and log backups are in this database. Im sorry for the typo.
thank everyone for your advices.
Have a nice day
June 4, 2009 at 8:19 am
That makes sense then.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply