Transaction log back and point in time recovery

  • SQL Server 2000 - Full database back up at 9:00pm; transaction log backup at 12:00pm. Internal database, therefore, database use normally from 7:00am - 9:00pm.

    Question 1) If backup mode is full; why doesn't it backup the transaction log and truncates / cleans it?

    Question 2) Transaction log is getting big at 8:00am - before the transaction log gets full, what is the best course of action and at what time and still be able to restore to point in time if anything happens to database?

    I was reading about log shrinking[/url] but

    Question 3) do not understand reason for the double DBCC SHRINKFILE

    Question 4) What is, if any the implications it'll have on point in time recovery if I do DBCC SHRINKFILE

    Thanks

  • You may want to read this article, Managing Transaction Logs[/url].

  • simple answer - do not shrink the log.

    Backup your transaction log more frequently to avoid it filling, so instead of one transaction log backup job at 12pm , run it say 1 or 2 hourly during the day.

    As to exactly how often that depends how much data you feel you can afford to lose, because you can always restore to the last transaction log backup. For a point in time restore that is the same as currently, just restore all the transaction logs in order, then do a point in time for the last log backup if necessary.

    The full backup gives you a restore start point, it does not truncate the log so following transaction log backups can be taken without a break in the restore chain,

    please read this : http://www.sqlservercentral.com/articles/Administration/64582/

    ---------------------------------------------------------------------

  • Thank you for the quick response.

    I read the article and it says "If (once the log backups have been setup and scheduled based on the database's RPO) the log is growing larger than is acceptable, then the log backup frequency can be increased in order to keep the size down."

    I'm confused ... how is it supposed to do that? In my situation, I have a transaction log file that is 1.5gb and it has gone through a few days of transaction log backup at 12:00pm. However, the size is still 1.5gb.

    I executed the following

    SELECT * FROM sysfiles;

    GO

    This is actually the concern. My log file is larger than my data file. That was the reason I started researching on Transaction Logs. For that matter, should I even be concern with my log file larger than my data file?

    In addition, I have a "Work in Progress to do" list from the former DBA to shrink log files on databases where the log file is larger than the data file.

  • your log file has grown larger than your data file because it was not being backed up often enough, therefore at some point it has had to grow to accommodate the information being written to it.

    The log backup removes the commited transactions from the log, freeing up space internally for reuse, this is what is meant by truncating. A log backup does not reduce the size of the file on disk, only shrinkfile does that.

    A log file larger than the data file is not a problem in itself if you have the disk file space to accomodate it. If you have a large no. of vlfs however that will slow up database recovery and restores.

    If you really want to shrink the log, find out the maximum log backup size when you instigate more frequent log backups and shrink the log to a size that will accomodate that (largest log backup is usually after index rebuilds). Do this shrink only once though.

    to find out how many VLFs you have run dbcc loginfo(yourdb) - the no. of rows returned is the the no. of vlfs - if its in the thousands you have too many.

    quick way to see space used within the log - dbcc sqlperf(logspace) - run that before and after a log backup to see the effect it has on the log.

    check your growth factors are sensible - not the default 10%. For your size log 64mb would be good, then each growth will give you 4 more vlfs.

    ---------------------------------------------------------------------

  • Here are the numbers after today's 12:00pm transaction log backup. Missed the pre-backup numbers.

    VLFs or dbcc loginfo(yourdb) returns 288 rows.

    dbcc sqlperf(logspace) returns Log Size of 1588 MB (or the 1.5GB I had mentioned); and 1.23xxx % Log Space Used.

    If the percentage used is averaging around, say 5% BEFORE transaction log backup; does that mean there's just a spike at one point in time of use? If so, does that also means I should be issuing SHRINKFILE commands from time to time, after the full backup?

    I'm not trying to 2nd guess you ... but I just do not understand why the following recommendations: "Do this shrink only once though."

    I understand that everytime a log file needs to auto expand, it takes up resources. But, if it's only using say 75MB (5% of 1.5GB); wouldn't it be good measures to shrink it from time to time - to save space not used 99% the time and the space allocated is only for 1 time use?

    And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?

    At least, that's what my noob dba brain is telling me.

    Thanks!

  • MSSQL_NOOB (12/31/2012)


    If the percentage used is averaging around, say 5% BEFORE transaction log backup; does that mean there's just a spike at one point in time of use?

    Maybe. You'd need to investigate and see if it was a one-time spike or if it's a regular spike. Spikes in log usage are often regular.

    I used to have a DB that had a 250GB log file. During the week it used 10% of that at most. On a sunday night, when we did imports, it reached 85%.

    If so, does that also means I should be issuing SHRINKFILE commands from time to time, after the full backup?

    Nope. Do not schedule shrink operations. Once off, or after uncommon operations like large once-off data imports or archiving processes.

    I'm not trying to 2nd guess you ... but I just do not understand why the following recommendations: "Do this shrink only once though."

    Because growing a log is an expensive operation and causes internal fragmentation if the growth increments are inappropriate. Shrink is also an expensive operation.

    So shrink often and you get into this nice shrink-grow cycle that takes huge amounts of resources and time, fragments the log and achieves nothing.

    I understand that everytime a log file needs to auto expand, it takes up resources. But, if it's only using say 75MB (5% of 1.5GB); wouldn't it be good measures to shrink it from time to time - to save space not used 99% the time and the space allocated is only for 1 time use?

    Not really. There's no penalty or problem from lots of free space. If the log really needs that space, it'll just regrow (and if the space isn't there you'll get log full errors and failed transactions)

    And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?

    What's the full backup got to do with anything? It doesn't truncate the log, it doesn't shrink the log.

    SQL is not MS Access that requires regular compact and repair to function properly.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • MSSQL_NOOB (12/31/2012)


    SQL Server 2000 - Full database back up at 9:00pm; transaction log backup at 12:00pm.

    Backing up the log once a day? That's useless for recovery. It means you have a maximum data loss of 24 hours in the case of a disaster. If that's acceptable, switch to simple recovery and leave the log alone. If that's not acceptable, you need more frequent log backups.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • MSSQL_NOOB (12/31/2012)


    Here are the numbers after today's 12:00pm transaction log backup. Missed the pre-backup numbers.

    VLFs or dbcc loginfo(yourdb) returns 288 rows.

    dbcc sqlperf(logspace) returns Log Size of 1588 MB (or the 1.5GB I had mentioned); and 1.23xxx % Log Space Used.

    check the size of the log backup to tell you how much space was used in the log file.

    288 VLFs would not concern me.

    And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?

    Its not a 'cleanup', its a logically consistent snapshot of your data, but yes you still need to take regular full backups as well as log backups, if only so you don't have too many log backups to work through in a recovery scenario. Work out a backup strategy that gives you the recovery you require. Its being able to recover the database within acceptable data loss limits (to your organisation) that matters.

    ---------------------------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply