LDF FILE DELETION

Question

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?

Answer 1

You need to read up on how to do SQL Server backups and how Recovery Models work: https://msdn.microsoft.com/en-us/library/ms187048.aspx

But briefly:

• If you have your database set to Full Recovery Mode then you need to do transaction log backups: used space in the transaction log will become available for new transactions.

• If you are in Simple Recovery Mode then the transaction log needs to be sized for your peak transient usage (or it will naturally reach this level).

Deleting your transaction log is a bad idea, especially if you do not know how it is being used.

Answer 2

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);