July 6, 2011 at 6:30 am
GilaMonster (7/6/2011)
Ol'SureHand (7/5/2011)
- What is this database Recovery Model? Is it (or can you change it to) SIMPLE? (ask first).If it isn't, you have a few other issues to sort out first and be careful before doing a full backup as this will break the transaction log backup chain.
Backups do not ever break the log chain, and I wouldn't consider being in a recovery model other than simple to be an 'issue'
If it is Simple, taking a full backup will not only clean up transaction log space (checkpoint would do that ), but will create a .BAK file that only has data and no unnecessary transactions.
The backup doesn't do that. The backup runs a checkpoint before it starts and that's what truncates the log, not the backup.
All backups contain some amount of log records, enough to recover the DB to a consistent point. That is no different in simple or full recovery.
Thanks, Gail, for crossing the t's and dotting the i's .. you have an admirable capacity to express complex issues in very simple & unambiguous terms. That is what I was trying to say ... but muddled it up.
In practical simplified terms, a full backup will achieve what is needed, i.e. a cleanup that I suspect does not get done, because the default settings for new databases set a FULL recovery model and the accidental DBA only does a full backup (resulting in uncontrolled tran log growth). I have seen this only too often.
However, dealing with differential or transaction log models is an "issue" for the accidental DBA as the change in recovery model (if not the full backup, thanks Perry!) will break the log chain and cause havoc down the line. (Also, you need to make sure the "extra" backup is known to the DBA so new tran logs apply to this irregular backup ... ).
Furthermore, to achieve the cleanup with other than "simple" recovery modes, he also needs to run the other steps (diff/bulk/tran log backups), otherwise it's a fail again.
Once again, thanks for the corrections! You have set a high standard in your previous article on backups and their relationship to recovery models, this "least understood" thing, and the OP would do well to refer to those seminal explanations before doing anything. Sorry I don't have the link to your article to post - One should keep a link handy at all times!
Here is the link to Gail's article explaining the relationship between recovery mode and backup type: http://www.sqlservercentral.com/articles/64582/[/url]
July 6, 2011 at 6:50 am
Ol'SureHand (7/6/2011)
In practical simplified terms, a full backup will achieve what is needed, i.e. a cleanup that I suspect does not get done, because the default settings for new databases set a FULL recovery model and the accidental DBA only does a full backup (resulting in uncontrolled tran log growth).
Huh???
A backup (full) does just that. Back up. Nothing else. I don't know what cleanup you're alluding to.
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
July 6, 2011 at 7:10 am
GilaMonster (7/6/2011)
Ol'SureHand (7/6/2011)
In practical simplified terms, a full backup will achieve what is needed, i.e. a cleanup that I suspect does not get done, because the default settings for new databases set a FULL recovery model and the accidental DBA only does a full backup (resulting in uncontrolled tran log growth).Huh???
A backup (full) does just that. Back up. Nothing else. I don't know what cleanup you're alluding to.
Gail, as you said: The backup runs a checkpoint before it starts and that's what truncates the log, not the backup.
Perhaps that's what happens technically; practically, I don't know anyone who goes around triggering "checkpoints" so in practical terms your backup is WHEN the log gets truncated or cleaned up (by triggering checkpoints or otherwise).
But if you have the database in FULL recovery mode and ONLY do FULL backups (and you don't otherwise issue checkpoints), the transaction log never gets truncated, is that correct? If so, that's what I am trying to say.
So, to assist the OP with his question, since backup/restore is the recommended way to move the DB to another server where he is already short of space, the least he can do is get rid of the inactive parts of the transaction log so the size of the .BAK file gets smaller.
July 6, 2011 at 7:17 am
Ol'SureHand (7/6/2011)
Gail, as you said: The backup runs a checkpoint before it starts and that's what truncates the log, not the backup.Perhaps that's what happens technically; practically, I don't know anyone who goes around triggering "checkpoints" so in practical terms your backup is WHEN the log gets truncated or cleaned up (by triggering checkpoints or otherwise).
SQL runs checkpoints automatically. The interval varies, but it's usually every couple minutes.
But if you have the database in FULL recovery mode and ONLY do FULL backups (and you don't otherwise issue checkpoints), the transaction log never gets truncated, is that correct? If so, that's what I am trying to say.
In full recovery it takes a lock backup to truncate the log. Only a log backup.
So, to assist the OP with his question, since backup/restore is the recommended way to move the DB to another server where he is already short of space, the least he can do is get rid of the inactive parts of the transaction log so the size of the .BAK file gets smaller.
Inactive portions of the log are not included in backups. SQL just back up enough of the log to restore the database in a consistent state,
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply