0

I have a set of database on full recovery model, my backup schedule looks like this:

  • 1 Weekly Full Backup
  • 1 Daily Log Backup

Full backups are about 10GB, log backups are about 500mb per day.

What happens is that right now, I'm having a DataFile of 10GB but a log file of 30GB...

Could be an option to shrink the log file? Is this a good practice? I have read that is not recommended but I don't know the exact reason.

Thanks!

J1mmy
  • 520
  • 1
  • 7
  • 21

1 Answers1

0

I would recommend backing up the transaction log more frequently than once a day, possibly every 15 minutes.

It may be that a single very large transaction has blown up its size. You can monitor how full the transaction log gets during the day (in between log backups) using the command

dbcc sqlperf(logspace)

And yes, you can shrink the log, using a command like this, for example, if you want to shrink it to 1000MB:-

use YourDatabaseName
go

dbcc shrinkfile(2,1000)

Weekly full database backups exposes you to data loss; I would also recommend backing your database more frequently than weekly.

Thomas Pullen
  • 472
  • 2
  • 7
  • I will check the transaction log growths during the day to see what happens with it.

    The problem with the Backups is that I will not have enough disk space to hold Daily full backups that why I started with Weekly Full and Daily T-Log

     – J1mmy May 26 '16 at 09:34
  • Tell your boss, you need more disk space. Or if he chooses not to, he is exposing himself to data loss. That might focus his mind! – Thomas Pullen May 26 '16 at 09:40
  • May I ask why that exposure to data loss? I mean, if something happens I can always restore the whole chain to get to the nearest point in time. And if the Backup is corrupted, then the full backup could probably also be? Or is it the Transaction backup more suitable to be corrupted than the Full? – J1mmy May 26 '16 at 10:07
  • 1
    You can "always" restore the whole chain? How often do you test that?

    Restoring a week's tranny logs takes a LOT longer than just one day's worth. You need to think not just about what you might lose, but also about how LONG it's gonna take you to recover. Downtime = sweaty armpits time for both you and your boss, and your users, and your company's shareholders.

    What are you trying to protect yourself from? Disaster? Corruption? Accidental data modification? You need to think about all the possible modes of failure, not just the most obvious.

     – Thomas Pullen May 26 '16 at 13:25
  • Good Advise, I will think about that... – J1mmy May 26 '16 at 15:19