Log File Shrinking

Hi All

In our PRODUCTION environment we have recently disabled TLOG and differential backup due to problems in our storage for time period. As our core DB's in full recovery model log files are growing, to prevent this, right now I was shrinking the log file twice in a day.As shrink doesn't work directly sometimes I was changing the recovery to simple and shrinking the log file and altering model to full again. could you please check the above way iam using and correct me if necessary.

Right now we are taking  full backup daily in the night

with regards

ram  


  • Edited by ramdb55 5 hours 35 minutes ago
February 27th, 2015 12:55am

I don't think it is prudent to disable diff and tlog backups. Disk is cheap these days. If you have storage problems there are other ways you can circumvent the problem:

  1. Backup locally (I hope you are using backup compression) and immediately offload the backup files to a network storage
  2. Run differential and tlogs backups as usual but only keep the last differential backup file and all the TLog files since the last differential backup until the storage problems are resolved/until you procure more storage
Free Windows Admin Tool Kit Click here and download it now
February 27th, 2015 1:21am

thanks for reply, our storage problem is not about the space as our exchange and DB's are sharing common storage in backend it is generating high IOP's in peak hours. Now they have decided to move the exchange to different  storage,so for the time being to control those high IOP's we are instructed from vendor to stop backups in business hours as these backups will read the disks. 

with regards,

ram

  
February 27th, 2015 1:36am

I was changing the recovery to simple and shrinking the log file and altering model to full again.

Hello,

What should that be good for? If you don't run any log backup then you can set your DB to recovery model "Simple"; without log backup you can't every start a point-in-time restore at all.

Free Windows Admin Tool Kit Click here and download it now
February 27th, 2015 3:08am

Hi,

You were doing blunder. First please note when in full recovery model ONLY transaction log backup can truncate logs and would make logs reusable which ultimately can be *Cut OUT* in shrink operation and given back to disk. Since you were not taking Trn log backup the log was not going to truncate hence shrinking would just not work.

You should have asked to storage team that if we do not take log backup logs will grow so what ever was the issue, should have been fixed on priority

regarding changing recovery model I guess this is only what you could have done but hope you were aware you would loose point in time recovery when you changed to simple recovery model.

All in all, get back system to its previous state and start taking Log backups

February 27th, 2015 5:50am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics