SQL Server Logs are Full

In SQL Server 2012R2 I found that my Log files were always getting so big that they would fill the L drive. So I wrote a script that will Truncate all the Log files. You can also do this on each individual database by right clicking, select Shrink, then Files. Click OK. See below for more details.

Also Check this URL: What is the command to truncate a SQL Server log file? - Stack Overflow

In management studio:

  • Don't do this on a live environment, but to ensure you shrink your dev db as much as you can:
    • Right-click the database, choose Properties then Options
    • Make sure "Recovery model" is set to "Simple" not "Full"
    • Click OK
  • Right-click the database again, choose Tasks -> Shrink -> Files
  • Change file type to "Log"
  • Click OK.

Alternatively, the SQL to do it:

USE DatabaseName
ALTER
DATABASE DatabaseName SET RECOVERY SIMPLE
DBCC SHRINKFILE (DatabaseName_Log, 1)
ALTER DATABASE DatabaseName SET 
RECOVERY FULL 

Add comment

Loading