0

I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server. All the transactions are occurring on the primary SQL server. In primary , i have Full Backup running every day at night and Transaction log backup running every one hour. Transaction replication is enabled in Primary to fail-over server.

In fail over server there i didn't set up full backup and transaction log backup.Recently i got alert that the drive in which LDF file is stored in fail over server is full.

That is when it clicked to me,that in fail-over i am not running regular Full back and Transaction log backup.(Recovery Model is Full Recovery) which is causing LDF file to grow and not truncate.

So i did a full backup of the database and then a transaction log backup ,but the LDF file size is stil the same.So i did a database shrink operation(i know that shrinking is not recommended) but still the LDF file size is same.

How can i reduce the LDF file size? Please help.

user9516827
  • 1,315
  • 2
  • 15
  • 35
  • I have seen this post,but my problem is even after doing full back and transaction log backup the LDF file is still big. – user9516827 Nov 21 '18 at 20:21
  • The ldf file size will remain the same, if you open a new query window and execute sqlperf(logspace) you will see how full the log file is. If it is cleared by the backup and you want to reduce the size and you know for sure that it will not grow out again, you could look into shrinking it in a maintenance window. – Randi Vertongen Nov 21 '18 at 21:12
  • Hi,fixed the issue.The issue was so silly.The initial LDF file size set was so big.That is why i think doing transaction log backup/shrinking was not taking effect. – user9516827 Nov 21 '18 at 21:14
  • 1
    Backup doesn't shrink the file. And it doesn't have to do with the initial file size. SQL Server doesn't even remember the initial file size after creation. The GUI (SSMS) incorrectly have the word "Initial" in there, something I have complained about for a long time. And finally, in SSMS 18, the word has been removed from the GUI. So, you just had to do a SHRINK, something which is performed if you specify a smaller size than the current size in the SSMS GUI. – Tibor Karaszi Nov 22 '18 at 09:43

0 Answers0