Results 1 to 15 of 15

Thread: Backups

  1. #1
    Join Date
    Sep 2008
    Posts
    11

    Backups

    Hi all. I'm seeing an issue with my Backup directory. Currently I have maintenance running on all of my databases to run complete backups daily for the databases and transaction logs. They are set to go to "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" with a ".BAK" extension. I also have it set to remove files older than 5 days.

    I noticed the directory was growing to over 100GB's. I went to the backup directory and noticed I had 700+ files with a .TRN extension. These are totally to over 85GB's. The remainder files are 5 days worth of backups.

    My question is what exactly are the .TRN files (I'm an SQL noob). I would like to free up some space on this server, but I don't want to just go and delete stuff. Please advise. Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    These are transaction log backup files. Did you setup maintenace plan to delete older transaction log backup files as well?

  3. #3
    Join Date
    Sep 2008
    Posts
    11
    Thanks for the reply skhanal. I checked again and see that the Transaction Logs are setup to go to "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP", but are also configured to "remove files older than 5 days". It's also set on a schedule of M-Saturday at 12AM.

    Backups do the same, except they are setup M-Sunday at 2AM. I verified that there are no backup's in there older than 5 days. What's the deal?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Take look at plan's log file, it should tell you why didn't delete old log backup files.

  5. #5
    Join Date
    Sep 2008
    Posts
    11
    When I look in the Maintenance Plan log, I see this:

    "Backup can not be performed on this database. This sub task is ignored"

    No error number or anything though.

  6. #6
    Join Date
    Sep 2008
    Posts
    11
    After googling the error, I get this: http://support.microsoft.com/kb/303229

    When I run a query to show the Recovery mode, I see that the three databases that are failing to delete the transaction logs are in "Full" mode. The ones that are working correctly are in "Simple".. which is the opposite of the Microsoft KB. Should I change them to all be Simple?

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can't backup transaction log for databases in Simple mode.

    Uncheck all databases with Simple mode from maintenance plan.

  8. #8
    Join Date
    Sep 2008
    Posts
    11
    Okay, I think I'm confused now. I have 13 databases, in which 10 are in SIMPLE mode. For the 10 that are in simple mode, I do not have any ".TRN" files in the backup directory. For the 3 that are in FULL mode, I have 85+GB's of ".TRN" files.

    If I convert all the SIMPLE ones to FULL, wouldn't I be flooded with even more ".TRN" files in that directory?

    Or is it that the ones that are in FULL mode ARE actually backing up, they just aren't rotating out older logs?

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Do you use single plan for all those dbs? If so, that's why plan doesn't delete old log backup files. Create separate plans, one for dbs in simple recovery model and the other for dbs in full recovery model.

  10. #10
    Join Date
    Dec 2004
    Posts
    502
    Also, you really need to learn about the different recovery modes (simple, bulk-logged, and full) and understand what the differences are. It's not just a simple matter of making everything simple or full or whatever based on getting rid of errors in the maintenance plans. There are many factors involved in deciding what recovery mode to put a database in. For example, one is generally deciding how much data loss can be tolerated with that particular database. There are too many others to get into here, so I would suggest really studying this subject. I know you stated that you are a SQL noob, but if you are tasked with this responsibility, it's important to know these basic concepts.

  11. #11
    Join Date
    Sep 2008
    Posts
    11
    I have a single Maintenance plan for all of my DB's. And 10 of those 13 are in SIMPLE mode.

    And nosepicker, I cannot just change the type from SIMPLE to FULL? Your saying there are things set that way for a reason?

    And yes, I will get to studying

    EDIT: I did do a little research to see the differences between the different recovery models. I found this article on the MSDN: http://msdn.microsoft.com/en-us/library/ms178052.aspx

    It says you can switch from SIMPLE to FULL, but is this okay to do? Will it effect any of my data?
    Last edited by goosed; 10-21-2008 at 10:57 AM.

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    You can change db recovery model from any one to another, that will not affect data in the db. But do affect log file usage, backup plan and data recovery mathod. To solve your problem, either create another maintenance plan or change db recovery model.

  13. #13
    Join Date
    Sep 2008
    Posts
    11
    Hey all, I hate to beat a dead horse but I wanted to follow up on my issue. I did create a new Maintenance Plan that does not backup transaction logs for databases in SIMPLE mode.

    The other databases in FULL mode are using the older Maintenance Plan that does incorporate transaction log backups.

    Now I do have that setup to delete older transaction log backups after 5 days. However I noticed that I still have months of older transaction logs laying around. Is it safe to delete older logs files?

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Yes if you don't need them.

  15. #15
    Join Date
    Sep 2008
    Posts
    11
    I just checked and saw that it successfully resolved the problem. With the new maintenance plan, one database had 387 tlog backups deleted.

    Thank you again for the help rmiao!

Posting Permissions

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