The problem occurs very frequent in the environments where Log Shipping is configured. Many times log files on disk get larger due to large SQL processes eating extra disk space This makes DBA to identify the log files which are restored on destination server and are no more required on source server , but are still occupying unnecessary space on source server disk. With this the database that are using same disk in which logs are saved can go in suspect mode If it would not be taken care in time than end users\work would be affected very badly . So to remove log files ,DBA has to first identify the primary and all the secondary servers involved in log shipping. Secondly, which all logs are restored on secondary servers , helping to identify which logs are no more required. So to resolve issues well in time, the DBA will use the automated script to do the job well in short time. This is very typical for any DBA to handle this type of situation in minimum time.
HOW TO USE:
There are two separate scripts for primary and secondary log shipping server. Execute the script accordingly or schedule it as job.
Change the value of "@DriveSpace" parameter to the Percent % free space you want on drive. At present it is set to 20. Which will leave 20% free space on drive.