Log files increasing in Log shipping

Question

Hi All

I have a disk space problem after configuring Log shipping.

Log shipping is configured on one of my database for every 15mins. Now the size on the server is getting low. What i should do in this scenario ?

Shall i shrink the log files or truncate. If i truncate the log files log shipping breaks.

---

Navakanth

Answer 1

Hi All

I have a disk space problem after configuring Log shipping.

Log shipping is configured on one of my database for every 15mins. Now the size on the server is getting low. What i should do in this scenario ?

Shall i shrink the log files or truncate. If i truncate the log files log shipping breaks.

---

Navakanth

Is your log shipping works properly, if log backup happens and restores on secondary server then log size should not grow, also analyze the output of below query, it would give you some clue why your log size is growing.

SELECT log_reuse_wait_desc FROM sys.databases

also see is there any open transactions in your database

DBCC OPENTRAN('Database_Name')

---

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

Answer 2

Hi Navakanth,

It is not recommended to truncate the log files as it would break the log chain, This would caused the log shipping to break. You may need to reconfigure the log shipping once again to get it working.

You can try shrinking the log file using the NOTRUNCATE option.

for eg., DBCC SHRINKFILE (  ‘Database_log’ , NOTRUNCATE)

Regards

Sathish S N

Answer 3

Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

Can we delete the older logs by leaving last 10 log files ???

---

Navakanth

Answer 4

Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

Can we delete the older logs by leaving last 10 log files ???

---

Navakanth

are you talking about log backup drive, if so then you can mention in your log shipping configuration how long you want to retain log files.

---

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

Answer 5

Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

Can we delete the older logs by leaving last 10 log files ???

---

Navakanth

Hello,

You can delete transaction log backups which are applied on secondary.You can use below two views to check status or log backed up and aplied.You can also RK on sql server go to reports...standard reports and see log shipping report pn both servers.

--on primary
use msdb
go
select * from dbo.log_shipping_monitor_primary
--On secondary
use msdb
go
select * from dbo.log_shipping_monitor_secondary

You can shrink log files but i dont think it will be of much help.Problem here is disk space I guess so you might consider deleting files already aplied on primary.Or you can move transaction log files  copied to secondary( but still not restored) to different location just to create space.

Moreover please also try to find out what operation caused massive log file

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers