0

I have database of 800GB average but when I run a backup job (of system and user databases) the transaction log backup reports a failure: There is not enough space on the disk.

When I make a transaction log backup the size of this would decrease or do I need a shrink (after or before run a backup)?

Tom V
  • 15,670
  • 7
  • 63
  • 86
Diego Flores
  • 449
  • 2
  • 7
  • 15
  • And is there enough space on the disk you are writing the backup to? Also, your database size has nothing to do with your transaction log backup size. – Tom V Aug 25 '16 at 14:41
  • When I execute the backup no, but when manually execute a shrink after the backup, yes. I need automate this. – Diego Flores Aug 25 '16 at 14:44
  • You are going to need to put a lot more detail in the question to get good answers to this question. – Tom V Aug 25 '16 at 14:45

1 Answers1

1

Shrinking is bad for performance and is a waste of time since it's just going to grow again. You need to add more disk space to accommodate the backups that you need. If you don't care about point-in-time recovery for the database, which it sounds like may be the case since there isn't enough storage even for a LOG backup, switch your recovery model to SIMPLE. With SIMPLE, you won't need LOG backups. But which recovery model you need is dependent upon your business needs of the database.

Tara Kizer
  • 5,956
  • 1
  • 15
  • 28
  • Tara, suppose my database requires full recovery mode. When you run a log backup, their size decreases or grow forever? – Diego Flores Aug 25 '16 at 14:56
  • After a log file backup the size of the file will not shrink, the space inside the file is just marked as "free" and can be written over. As Tara mentioned shrinking files is a bad idea in most cases. – MrTCS Aug 25 '16 at 15:13
  • @diegoxfs, check out the link added to your question that answers this often asked question. – Tara Kizer Aug 25 '16 at 15:19