July 25, 2007 at 7:50 am
Actually, I'm sure Backup Exec can backup open files with no problems. Open text files, .doc files, .xls files. All of which are not SQL Database files.
So you just need to make the point that SQL Database files are not like other files, Microsoft made sure of this, and that while you can back them up while they are attached to the server, you're not going to get anything useful out of them and will never be able restore/attach from those backups.
BTW, the 16KB transaction backup is 2 pages worth of data which is probably the smallest your backup will ever go. The 81KB is approximately 11 pages worth of data, probably generated from regular SQL Server background processes going on during the time the server is up. So I wouldn't worry about the fluctuating sizes. It's probably normal.
July 25, 2007 at 8:22 am
I'm well aware of the fact that the tran log will grow if not backed up. When RightFax was installed on the old SS2000 server, there was no backup at all ("we don't need no stinkin' backups for faxes") and as a result, the mdf was only 18 Megs but the ldf was 5 Gigs if you can believe that. I just added it to the existing maintenance plan & the size went way down.
BTW - I'm not getting any email notifications on my watched threads, is it just me? or are we having the Beta blues.
July 25, 2007 at 8:25 am
Re-click on watch this topic. Sometimes that flag gets deleted. I e-mailed Steve about this and he apparently can't figure this one out (or doesn't have time to anyways).
July 25, 2007 at 8:51 am
Turns out my web host is having trouble with the email server, so in my case it has nothing to do with the list. My public email address at my domain is forwarded to a private address, but the host email server is down.
July 25, 2007 at 9:56 am
Getting back on-topic, no pun intended.
On the old SS2000 server that did have a maintenance plan, it seems that the ldf files would usually run about 1/3 the size of the mdf files. I'm thinking that if the tran log backups are being done on proper schedule, that same ratio would apply to 2005.
Is that reasonable, and would you call that a best practice, as far as how to decide how often to schedule tran log backups?
BTW my email notifications are back online, I just got all 10 for this thread alone.
July 25, 2007 at 10:19 am
Without testing, I honestly couldn't answer you on that. And truly, I don't think there is a ratio of database to transaction log out there that is a standard.
Transaction log sizes are supposed to be based solely on 1) Activity of changes / inserts / deletes, 2) frequency of transaction log backups, and 3) miscellaneous SQL Server engine stuff that happens on your database depending on what options you have set. Given that SQL Server 2005 has so many different things going on in the backend, I wouldn't advise making that assumption. In fact, on a 26GB database using Litespeed to run and compress our backups, my breakdown is:
Complete database backup file: 4 GB per night (or close to it)
TransLog backup file: between 29 MB and 1 MB during business hours. And we do hourly trans log backups.
Which doesn't look like a consistant 1:3 ratio to me.
July 25, 2007 at 12:18 pm
I'm on-site at the customer right now. Sure enough, I right-click the db in SSMS and select Properties - the last tran log backup was when I did one manually on 7/10, and right now the ldf is larger than the mdf. So Backup Exec is not backing up what needs to be backed up for a healthy server.
You would probably agree that ldf > mdf is a really bad situation, yes?
July 26, 2007 at 4:00 am
Oh, absolutely. And you might want to check the properties of the log too, to make sure that it isn't set to unlimited growth. An out of control Translog can kill your hard drive space. And I'm sure you already know that once the hard drive space is full, SQL Server is NOT starting up.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply