Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Maintenance-Backup question

    Hi,
    I have a SQl database, when I view its properties from Enterprise manager, it's size is about 159 MB. I'm pretty sure I don't have that mucj data in it.

    I know that Transaction log files grow over time, but I don't know how to control its growth? Is there a way to delete logs that are unecessary?
    By backing up Transaction logs in a maintenance plan will the problem be solved?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if you don't need transaction logs change your recovery model to Simple, that way the transaction logs will be truncated at checkpoint.

    If you need transaction logs then you should be backing them up on a regular basis. After backup you can truncate the logs. See BOL and maintenance plan for syntax.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply,
    I have read BOL's explanation of backups and recovery models, however I'm still confused.

    I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
    I have a maintenance plan that backs up both Log files and the database on a daily bases.

    My confusion is mainly on the issue of Full versus other types of recovery models.

    I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

    "I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

    Is this strategy correct?


    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Ah no.

    Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

    My suggestion is to create a schedule of full backups and transaction log backups.

    All transaction logs can be deleted AFTER a full backup is again taken.

    Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

    You can specify al this in the maintance plan.

    Think about this and poste back.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Also, lookup "differential backups" in BOL, another good example of a backup strategy.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    A log dump is what I wanted to do, how do I specify a log dump?
    Is a log dump another term for backing up transactions logs?







    Originally posted by Paul Young
    Ah no.

    Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

    My suggestion is to create a schedule of full backups and transaction log backups.

    All transaction logs can be deleted AFTER a full backup is again taken.

    Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

    You can specify al this in the maintance plan.

    Think about this and poste back.
    Last edited by Sia; 05-07-03 at 19:07.

  7. #7
    Join Date
    Apr 2003
    Posts
    30
    After backing up, use dbcc shrink file to shrink log file. Since DBCC shrinkfile only shrink from the end of the log file, sometime you have to intentionally do some operations to move the log to the start of the log file. Normally, after two times of backup and dbcc shrink, the log file will be shrinked dramatiscally.

    Search google for Andrew Zanevsky, he wrote a good article and script of how to autamatically do the above steps.

    Here's header of this script.
    use master
    go
    if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
    go
    create proc sp_force_shrink_log
    /*
    ************************************************** ***********
    Name: sp_force_shrink_log
    Description:
    Shrink transaction log of the current database in SQL Server 7.0.
    Switch context to proper db to execute.
    Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
    exec pubs..sp_force_shrink_log

    Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
    zanevsky@azdatabases.com

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Originally posted by Sia
    A log dump is what I wanted to do, how do I specify a log dump?
    Is a log dump another term for backing up transactions logs?
    Yes, refer to BOL for Tlog backups.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Sia, finally found the article in BOL... look up "transaction logs, truncating" This should help you out.

    Yes, log dump = backing up transaction log.

    You will need to follow jzhu suggestion on shrinking the log files if yo uhave auto shrink turned off. IMNotSoHO I would wathc the log files and only shrink is space were a problem. If you consistantly need 1 gig of log space why go through the cycle of shrinking and growing? I shrink my logs only once a month.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your replay Paul.
    I think I got it!

  11. #11
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    A little confused

    Paul,
    Thanks for your recent replys.

    I have scheduled a Database and Log backup every day at 1 and 2 am.

    The thing is that, before I do the Log backup my database had the following properties:
    Space alloacted for data file was 50MB
    Space alloacted for Log file was about 100 MB
    availble space was about 70MB.

    The I did a manual database and Log backup which resulted in the following db properties

    Space alloacted for data file =50MB
    Space alloacted for Log file =50MB
    availble space= 100MB

    Its now about 2 days that I'm doing the Log and Data backup using a scheduled maintenance plan.
    I have the following DB Properties:

    Space alloacted for data file =50MB
    Space alloacted for Log file =2 MB
    availble space= 14MB


    =>My questions:

    Why did the Log File size reduced dramatically after 2 backups with the maintenance plan but was not reduced that much when I did the first 2 backups manually.

    Why is the "availble space" reduced!
    My guess is that the availble space is calculated using the allocated space and since I have autogrowth it woud increase itself when the file reaches the limit.

    I would appreciate if you let me know I'm on the right track or not.

    Thanks

    Originally posted by Sia
    Thanks for your reply,
    I have read BOL's explanation of backups and recovery models, however I'm still confused.

    I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
    I have a maintenance plan that backs up both Log files and the database on a daily bases.

    My confusion is mainly on the issue of Full versus other types of recovery models.

    I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

    "I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

    Is this strategy correct?


    Thanks

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

    As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

    Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thank you for your help!

    Originally posted by Paul Young
    Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

    As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

    Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •