August 20, 2012 at 9:28 am
Hi,
I seem to have caught amnesia.
I understand that frequent transaction log backups help maintain the size of a transaction log.
However do Full backups help anything with the transaction log. Or does it simply just restore the transaction log as one big file if you don't run any tran log backups previously.
Thanks
August 20, 2012 at 9:43 am
smallmoney (8/20/2012)
Hi,I seem to have caught amnesia.
I understand that frequent transaction log backups help maintain the size of a transaction log.
However do Full backups help anything with the transaction log. Or does it simply just restore the transaction log as one big file if you don't run any tran log backups previously.
Thanks
Full backups do not truncate (mark as reusable) any VLFs (Virtual Log Files). It backups up enough of the t-log to provide a consistent database as of the end of the backup. If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.
August 20, 2012 at 9:43 am
Full backups do not affect the transaction log.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2012 at 9:45 am
Lynn Pettis (8/20/2012)
If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.
It would only backup the 21 GB if there's an open transaction or unreplicated statement at the beginning of the 21 GB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2012 at 10:33 am
Just a side note. Be careful about how you think about log backups. They're not done to maintain the log size or shrink logs or anything like they. Log backups are only a part of the point in time recovery process. Nothing else. If all you're concerned with is log size, just use simple recovery (not recommending that from a business & DR stand point mind you, just that if you don't care about business & DR, it's a lot easier).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 10:56 am
GilaMonster (8/20/2012)
Lynn Pettis (8/20/2012)
If this means that it backs up a 21 GB log file for a 20 MB database because no t-log backups have been run between full backups, then yes that is what happens.It would only backup the 21 GB if there's an open transaction or unreplicated statement at the beginning of the 21 GB.
True, guess I really should have stayed in bed longer. But I do know that if your t-log file has grown from say 8 GB (normal size) to 30 GB (due to activity) that even if there were no active transactions the t-log file restored will be 30 GB in physical size. This killed me several times during restores to a space limited server until I found out that the other DBA was rebuilding every index on every table in that database every night. I fixed that quickly.
August 20, 2012 at 11:01 am
Yup, the DB will be recreated exactly as it was at the time of backup. Caught me too, had a 50GB backup file that needed 120GB to restore (empty space in DB, empty space in log)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2012 at 12:52 pm
GilaMonster (8/20/2012)
Yup, the DB will be recreated exactly as it was at the time of backup. Caught me too, had a 50GB backup file that needed 120GB to restore (empty space in DB, empty space in log)
Oh - the horror stories. Once had someone send me a backup that was a couple of gigabytes. When I went to restore - it would not because the actual data file was about 2GB and the transaction log needed 900GB.
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
August 20, 2012 at 3:48 pm
Transaction log maintenance can be a drag. The stairway about this topic is great.
-- Itzik Ben-Gan 2001
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply