I have a log shipping setup for DR. The transaction log file of one of
the secondary databases, which is in restoring mode, is 7 times larger
than the data file and is filling up the disk space. What are the
possible causes and fixes?
Possible causes can be a heavy transaction or bulk load ran on the primary database which causes its log file to grow and since these log files are shipped to secondary you see the growth on secondary log file as well.
Fixes: You will need to analyse the growth on the log file at primary (since log file at secondary is in restoring mode), if the growth rate is intended to grow that big or was it some sort of AD-HOC activity which made the log file blow that big on primary that caused the similar sort of growth on secondary.
In case: log file is intended to grow that big and there are task that will continue to perform you may consider the option of going with appropriate disk/storage accordingly, because no matter you shrink the task will occupy the same space back. In case its an AD-HOC task, you can go ahead and shrink the log file at primary ( making sure latest log backup is taken and copied to secondary) which should apply the same at secondary.
Note that it is the transaction log file of the secondary database in
the DR site that is growing fast.
Here if you meant that primary is stable with its log file and its only the secondary which is growing then , you need to consider looking at how log shipping is configured. I don't recall such scenario where primary log holds better while secondary grows that big, unless until log shipping is not set up correctly. If that's the case, consider setting up LS again with proper settings.
