Problems with ldf file

  • Hi

    I'm not an Administrator, but I hope I can explain my self.

    Like 3 months ago one of my partner erase the second most important table of our aplication.

    I wrote to see if I can recover the table and they said that  was nothing to do. But if I was having the Recovery Model to Full I would be able to recover something.

    At that time my recover modal was Simple. And we change it from simple to full, but my ldf is growing up very quick. Now my data files Space allocation is 29 MB, but my Transaction Log is 32061 MB.

    What can I do to avoid this file (ldf) to grow that much, and to minimize it actual size?

     

    Thanks.

     

     

     

     

     

     

     

     

  • take the transaction log back ups for every one hour

  • Read up in BOL on 'Truncating the Transaction Log'.  Also, if you need to physically shrink the ldf file to recover space, read up on dbcc shrinkfile. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Sorry to hear about the data loss. That's always tough to swallow...

    As far as the transaction log, when in Full or Bulk-Logged mode it will continue to record transactions (or bulk-logged activities) until backed up. That will empty it out a bit, but it won't shrink the actual file. To shrink it you can right click on the database in Enterprise Manager, go to shrink, then do a specific file and select the transaction log. You may have to repeat the process a couple of times to get it down to a reasonable size.

    Also, you need to make 1) A full database backup job (if you don't already have one) and 2) a transaction log backup job. Since you're new to this, the easiest thing is to create a database maintenance plan. Just walk through the wizard, it's pretty intuitive. Make sure you keep those transaction logs for some period of time - that's what let's you restore to a specific point in time.

    Just searching the web on "shrinking transaction log" will bring up a lot of similar comments and articles. When you have a chance, I'd study how the whole transaction log and backup/restore process works - trust me, it will be worth the time.

    Hope that helps a bit,

    J.

  • Did it take 3 months to determine the table was missing? If you knew right away, then you could have just restored the database to a different database and then moved the tables over to at least get you part of the way restored. Only new data added since the backup would be what you would have had to enter again.

    Just a little more info for future problems.

  • Thanks for all you help.

    What I did was to run this instruction on the Query Analyzer:

    backup log "bdValeE" with truncate_only

    The database name is "bdValeE"

    Then in the Enterprise manager:

    Right click on the data base, from the menu I selected  All Task + Shrink Database, then button Files, in the Database file: bdValeE_Log, then on the shrink action I selected Shrink file to  and I put the value that said on the MB(minimum 47 MB)

    Is that right?

     

     

     

  • When you strink a log you want to ensure the size makes sense, don't shrink it all the way down to where no data is. The log will grow as needed, based on your configuration that comes out of the box. You can restrict the size to a fixed amount, but you are better off knowing how often to run transaction log backups. For some systems we run every 5 minutes, but it depends on your needs. The more often you backup the small the log should be in theory, based on activity for transactions in your system.

    Are you backing up to the same drive, or a different NAS/SAN device? If backups are eating up your space you should look at getting the product from Quest, SQLLiteSpeed. We have 30+ servers and use it on all of them. It reduces the storage needs of our backups.

Viewing 7 posts - 1 through 6 (of 6 total)

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