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
  • 550
  • 1
  • 8
  • 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
  • 482
  • 2
  • 7