October 30, 2009 at 9:09 am
Hi,
I've a database in FULL mode to make Full, Differential and Transaction Log backups.
The database had 80GB in the data files and 70GB in the log files.
I made a Full backup and the log files kept the 70GB size.
Then, after 2h, I made Differential backup that had 700MB... so far so good...
But then I made a Transaction Log backup, just after the differential one, and the backup file had 6GB!!!
PS: I'm using compression.
After this I went to the database and right click for shrink files. The log file was taking up 70GB but 99% was empty, I checked this after the full backup and the transaction log backup. I told SQL to shrink the file but I still kept 20GB.
Shouldn't the transaction log be about 0B, since I made it just after the differential one?!
If I shrink a file that says it's 99% empty shouldn't it make it 0B or close?! Or because the database was being used the log had just a write "at the end of the file" that made it impossible to shrink to 0B?
Is there any command in the backup instruction to shrink the log file (after full or diff backups) or do I have to use DBCC SHRINKFILE?
There's no logic in the log backup being 6GB when the differential was just 700MB...
Thanks,
Pedro
October 30, 2009 at 10:19 am
None of the backups (full, differential, or log) shrink the physical log file. The log backup truncates the inactive part of the logical log to remove the backed up log record, but the full and differential do not. So, if you'd never backed up the log, it would be reasonable for the first one to be 6 GB, depending on how much transaction activity the database has.
The log file can't be shrunk to a size less than the virtual log files contained in it, so you'll never be able to shrink it to 0.
DBCC SHRINKFILE and DBCC SHRINKDATABASE are the only commands that will shrink the files in a database. See "Shrinking a database" in BOL.
Greg
October 30, 2009 at 10:21 am
Full and Differential backups do not truncate the transaction log. This is only done by transaction log backups. Please note that truncating the transaction log is NOT the same as shrinking the transaction log. To manage the size of the transaction log you have to schedule periodic transaction log backups.
To learn more, pleasse read the last article I reference in my signature block about Managing Transaction Logs.
October 30, 2009 at 11:29 am
basically what you're saying is that if I NEVER make a transaction log, cause I don't need for any reason and have full mode to make differential backups, the transaction log will never be truncated therefor shrink will never make it smaller?
Thanks,
Pedro
October 30, 2009 at 11:43 am
My purpose for this backup is to have the latest version of the data and be able to restore it at any time.
I'll never need to get the data on table X on yyyy/mm/dd hh:mm... I have the differential backup since the total backup takes a lot, so it's only made at midnight every day, and differential is made every hour.
Eventually we'd make transaction log backups every 30min or so to truncate the log...
Thanks,
Pedro
October 30, 2009 at 11:44 am
PiMané (10/30/2009)
basically what you're saying is that if I NEVER make a transaction log, cause I don't need for any reason and have full mode to make differential backups, the transaction log will never be truncated therefor shrink will never make it smaller?Thanks,
Pedro
Yes. If you never plan on making transaction log backups or need to do a point in time revocery of your data, then your database should really be using the SIMPLE recovery model, not the FULL or BULK_LOGGED recovery models.
Edit: Fix typo.
October 30, 2009 at 11:55 am
Thanks. Didn't notice that differential also was available with simple mode...
We are planning on having payments transactions on this database at a given time...
We were planing on creating a filegroup to store these "critical" tables and have a different backup strategy: Full (can only be of the whole database) every day, differential every hour (filegroup) and transaction log (filegroup) every 5 minutes. If anything went wrong we had to restore the full backup of that night, the latest differential and all the transaction log since the last differential. Is this possible? To have restores a full database backup and then only the differential of a certain filegroup?
And since we can have differential of a certain filegroup, and we don't want to restore to a point in time, only full restore, is it better to have the transaction log backups or only differential backups (the tables on this won't be very large or have many transaction, the 5 min interval for the backups if for caution only)? If we had only diff backups we could have the database on SIMPLE mode and not worry with log truncate.
Thanks,
Pedro
October 30, 2009 at 1:51 pm
If losing a day's data won't matter, then Simple mode and full backups are enough.
If losing an hour's data won't matter, the Simple mode, daily full and hourly diff is enough.
If losing any data is a problem, then I recommend Full recovery, daily full backups, and hourly log backups. You might have some slight value from diff backups in this situation, but I've done a lot of tests and I've usually found that full+log to point-in-time is faster than full+diff+log, even though it involves less log backups being run.
You might need to vary the frequency of the log backups, mainly to manage disk space, but I've usually found hourly works pretty well.
Combine that with keeping the data and log files on separate disks, and your odds of data loss are quite small. Add in an offsite backup solution (geographically separate server farm is best), and your odds of data loss become negligible.
If the database doesn't have a lot of transactions, then the log backups won't be very large, won't take long to run, and won't take long to recover.
- 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
October 30, 2009 at 4:15 pm
Hi,
Like I mentioned some table are critical and some aren't. But there's no way to tell SQL to do full mode on some tables and single on other...
Some tables, very large tables with millions of records, are from data that's obtained from outter sources, data sources from web services. That data doesn't change a lot, probably 100 records every week if so.
And even if we lost that data we could always use the webservice and get it all.
We have RAID10 for data, on FC disks on a SAN, RAID1 for log and RAID1 for tempdb. The backups will be made to the log or tempdb disks and then moved to tape.
Since we don't need point in time recovery we could use the single mode, but with our "critical" tables, problably will use full mode so we can have transaction logs backups in the specific data file group....
Just how bad is, for performance, to do after a transaction log backups a log shrink? Since the log has been truncated the shrink should be fast, no?!
Thanks,
Pedro
October 30, 2009 at 5:05 pm
Can't speak to using filegroup backups, I have never used them personally.
Regarding shrinking the t-logs after t-log backups, NO. It would simply have to grow again as transactions are written to the log. This will impact system performace and could result in file fragmentation of the t-log.
Have you read the article I recommended, and is referenced in my signature block regarding Managing Transaction Logs? If not, take the time to read it. Now.
October 30, 2009 at 5:31 pm
Pedro,
You cannot setup separate filegroups for full recovery model or simple. The recovery model is defined at the database level. If the database is set to full recovery model - frequent transaction log backups are required.
What is the goal here? Are you trying to reduce the time it takes to backup the system? Or, are you looking at trying to reduce the time it takes to restore? Either or these are not really decisions that are made by the DBA. These decisions are made by the business - which will drive what you do to meet the business requirements.
For example, if the business requirement is to be able to restore to a point in time within 4 hours - then you need to model your backup/restore strategy in such a way that you can restore a full backup, differentials and/or log backups in that time frame. If the requirement is to restore the system to within an hour (or two) of the outage - then you model your backup/restore strategy to meet that requirement.
Now, if it is not possible to restore the full backup in that time frame - then, you start looking at splitting the database into multiple filegroups. Then, your strategy changes so you are not backing up each filegroup daily - and you have the system setup/configured so you can restore a single filegroup as needed.
Either way - you need to figure out what the business requirements are and tailor your backup/restore process to meet those requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 30, 2009 at 6:15 pm
Thanks,
I've already scheduled a meeting with to product manager to figure out exactly what are the needs regarding point in time restore.
But yes, my main goal is to speed up backups and be able to restore the database as quick as possible.
Probably we'll be doing a full backup every night (this is a global app so "night" is very relative) and differential every hour with transaction log backups every five minutes to the "critical" tables' filegroup.
Should we schedule a transaction log backup to the other filegroups to truncate them so they don't grow indefinitely, even if we don't do anything with them?
I've to figure out, since the other filegroups are mainly of "read" tables how big they get and probably a 5 min transaction log, or 15 min, won't take that long...
Thanks all for the advices,
Pedro
@Lynn Pettis: sorry, haven't read the link's article yet... I'm on GMT so right now I'm already on weekend... but I'll surely read it tomorrow... Thanks..
October 30, 2009 at 7:08 pm
The transaction log is the transaction log. It doesn't matter if you have one filegroup for the data or 100 filegroups for the data. There aren't separate transaction log for each of the filegroups.
October 31, 2009 at 3:53 am
damn.. forgot bout that :S
Thanks...
So, if there's no need to point in time restore, the best for us is to have single mode, with full backups and diff backups to the "critical" table's filegroup (will be small since there won't be that many transctions). Is it viable to have 5min dif backups with compression? We'll be doing the backups to a FC disks with write cache, since it's in a SAN.
Thanks,
Pedro
October 31, 2009 at 7:12 am
PiMané (10/31/2009)
damn.. forgot bout that :SThanks...
So, if there's no need to point in time restore, the best for us is to have single mode, with full backups and diff backups to the "critical" table's filegroup (will be small since there won't be that many transctions). Is it viable to have 5min dif backups with compression? We'll be doing the backups to a FC disks with write cache, since it's in a SAN.
Thanks,
Pedro
You keep talking about doing a differential backup of a critical table's filegroup. I can't speak to you about filegroup backups, I don't do them as I have no need to do them.
A differential backup only backs up the changes since the last full backup. It doesn't matter where in the database or which filegroups. Again, you really need to read that article as well as BOL (Books Online, the SQL Server Help System). These will help you understand the differnet types of backups (and therefore restores).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply