Search code examples
sql-serverldf

LDF FILE DELETION

Is there a way by which I can delete periodically the LDF file generated as it takes upto 100 gb of my space and I want to free that space. Is there a way a procedure can be written that periodically deletes this LDF file?

Solution

  • you definitely can't delete LDF file only shrink to initial size. Step to shrink are

    1. stop replication (if any)

    2. set database to simply recovery mode (if is set to other)

    3. perform backup

    4. perform shrink

      Sql for step 1 you can find here (never used it) https://cavemansblog.wordpress.com/2012/03/12/startstop-sql-server-replication-agent/

    Sql for step 2 https://msdn.microsoft.com/en-us/library/ms189272.aspx

    USE master ;  
ALTER DATABASE youdatabase SET RECOVERY SIMPLE ;
...  
ALTER DATABASE youdatabase SET RECOVERY FULL ;

    Sql for step 3 https://msdn.microsoft.com/en-us/library/ms186865.aspx

    BACKUP DATABASE your_database TO DISK = 'place_your_path_here';

    Sql for step 4 https://msdn.microsoft.com/en-us/library/ms189493.aspx, btw B is complete script for your needs :-)

    DBCC SHRINKFILE (yourdabase_Log, 1);