May 12, 2009 at 8:26 am
Hi,
I would like to know how to estimate the disk space needed for starting to perfrom transactional log backups to my database.
Thanks
Shruti
May 12, 2009 at 8:55 am
Quite a few things to keep in mind when you are trying to estimate the space requirement.
What is your retention period?
Are you doing a Daily Back up?
What is the transaction count that you have?
Rest I think will probably be filled up by the Gurus...:-) These three came in mind.
-Roy
May 12, 2009 at 9:19 am
Everything you do is a guess here. It's hard to estimate this unless you know the transaction load.
And if you do, then why not just run a log backup every 15 minutes. The size of that log x 4 will roughly give you the hourly backup size. That x24 will give you the daily load.
You only need to keep logs in between full backups, so how often do you run those? That affects the calculation as well.
May 12, 2009 at 2:31 pm
Thank You all for your help. Based on all your views, I see that my current transaction log is 10 MB. So, would that mean, one transactional log backup would have a size of 10 MB ? Depending on this , I can use the math provided. (10*4*24) for estimating the daily load.
Secondly, if I do a transactional log backup not very often but just twice or thrice a day, would that affect my data loss window ? Would i lose data at the time of a point-in-time restore? What is the earliest time to when point of time restore can be done?
Thank you all
Shruti
May 12, 2009 at 2:37 pm
You will get the data till the last transaction Log was taken. Let us say you are doing transaction logs every 12 Hrs. First one at 12 Midnight and the next one at 12 in the noon, and let us say your DB is toasted at 11:59 AM, then you lose all the data that happened from Midnight till 11:59 AM.
When you say 10 MB, are talking about the log file size? Is your DB in Full Mode or simple mode?
-Roy
May 12, 2009 at 2:51 pm
ok i think i got that part. thanks ..
my d/b is currently in simple recovery mode and we have a full backup every night. 10 MB is the size of the transaction logs. I found it by dbcc sqlperf(logspace). so would that mean, a transactional log backup would occupy 10 MB of disk space?
thanks
shruti
May 12, 2009 at 3:15 pm
Not exactly. If you want to see the size of the Transaction log, you should set the DB mode as FULL. Only then will it write to transaction log and saved till you take a transaction log back up.
-Roy
May 12, 2009 at 3:34 pm
You ALWAYS want to have free space in your data and log files. Growing them is a relatively expensive process, so you have free space in there.
A 10MB log has nothing to do with the size of log backups. You'd have to take the log backups every hour (after switching to full mode) to get an idea of what the transaction load it. You might end up with 20kb or 9.9MB log backups. It's impossible for us to know.
May 13, 2009 at 6:23 am
shruti.ratnagirish (5/12/2009)
my d/b is currently in simple recovery mode and we have a full backup every night.
So, are you ammending your DR plan? May I ask you what made you to think about tlog backups/point-in-time when you are in Simple Recovery model? Could you please give us a brief idea about the load and processes on your server?
And Steve has given you a general idea that if you would have taken Tlogs every 15 mins but, the frequency of log backus might change according to your business model and thus your space requirements.
10 MB is the size of the transaction logs. I found it by dbcc sqlperf(logspace). so would that mean, a transactional log backup would occupy 10 MB of disk space?
No, We cannot derive it that way. Backups size really depends on the number of extents that have changed in the database not according to the tlog file size.
Ideally the disk space requirement that you need depends upon your DR plan and how long you would like to keep the backup files on your disk.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply