3

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

We had setup log backups for every 5 minutes, so there no problem with ldf file increasing.

We had setup Log shipping on Prod server(Primary) and DR Server(Secondary)

Now i have a question,

  1. Can i shrink the log file of the database on primary to 1 GB?
  2. Can i shrink the log file i.e. ldf without changing the recovery model of database to 1 GB?
  3. Can i shrink the log file without using NOTRUCATE option, if i use ssms wizard to shrink.
  4. Will shrinking cause for log file to grow more after shrinking?

Expedite answers would be appreciated

Thank you

Improve this question
3
  • 1
    Don't do it if it is not causing any issues. It is likely that it will grow again.
    – SQLPRODDBA
    Sep 8, 2016 at 11:05
  • I have a space of 99% unallocated .ldf file, we want .mdf and .ldf to be placed on separate disk drives, so i want to put .ldf file on 20 GB Disk drive, so if i do shrinking will this cause for log shipping to stop.
    – Siva
    Sep 8, 2016 at 11:15
  • No it will not read my answer. BUT i again reiterate don't shrink
    – Shanky
    Sep 8, 2016 at 11:27

1 Answer 1

Reset to default
3

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

Leave it as it is, the space would eventually be utilized without any autogrowth and that would really help your SQL Server transactions, so just DONT shrink.

FYI

  1. You can shrink log files on primary server
  2. DONT change the recovery model this would break logshipping. Yes you can shrink without changing recovery model
  3. The log file will eventually again grow so there is no sense in shrinking.
  4. You may use NO truncate but normal shrinking would just do fine. This is just to answer your queries I would still say do not shrink.

Further reading Why Does Transaction Log file Growing and Runs out of the Space

Improve this answer
1
  • Thank you, We have a frequent log backups which will control increase of log file (ldf) growth, and i greatly appreciate for answering all 4 questions and helping me to learn.
    – Siva
    Sep 8, 2016 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.