SQL server 2012 - Will data be lost if shrinked Log file

Question

SQL server 2012 - Will data be lost if shrinked Log file

santosh.mane

SSChasing Mays

Points: 611
More actions
February 22, 2018 at 7:55 am

#403135

Hi,
We are having production database and the log file size is reached to 120 GB, we are also taking transaction log backup after every 30 min.
We want to shrink the Database log file and the database is in Always On High Availability.
Please let me know how to shrink the Database Log file without losing the Production data.
Thanks & Regards,
Santosh
March 11, 2018 at 11:53 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

santosh.mane - Thursday, February 22, 2018 7:55 AM
Hi,
We are having production database and the log file size is reached to 120 GB, we are also taking transaction log backup after every 30 min.
We want to shrink the Database log file and the database is in Always On High Availability.
Please let me know how to shrink the Database Log file without losing the Production data.
Thanks & Regards,
Santosh

Shrinking the log file won't lose you any data but before you do that find out why the log is that size. It has grown to 120 gb because it needed to and if you shrink it it may have to grow to that size again.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Shrinking a log or database only removes pages that are unused by the system.

If you are taking log backups, check the sizes. That's the level of activity that will tell you the size you need for the log file.

If the log file is still growing despite the log backups, make sure you don't have a replica offline that might be preventing the log file from being reused. A log backup marks the page as available for reuse.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Check what, if anything, is preventing reuse in the log file like this:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase';

John

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

santosh.mane - Thursday, February 22, 2018 7:55 AM
Hi,
We are having production database and the log file size is reached to 120 GB, we are also taking transaction log backup after every 30 min.
We want to shrink the Database log file and the database is in Always On High Availability.
Please let me know how to shrink the Database Log file without losing the Production data.
Thanks & Regards,
Santosh

What is the initial log file size? Is the synchronization synchronous or asynchronous?
😎
The shrinking does not flush/loose any data, I'm more concerned that the VLF counts are too high 😉