Truncate Database Log files in SQL Server


Truncate database Log files, Shrink database

Hi,
I was doing some cleanup of data from my database, but when i started deleting records from the tables i got a windows popup saying Low disk space, i thought what is this happening i am deleting the data from my database, it should free up disk space but it is prompting low disk space.

oh i see my database log file is growing as i am deleting the data, my log file size became around 1 GB, ok so what to do??, i decided to truncate the log file, So below is simple SQL Query which can truncate log file in a min.

 
use MyDatabaseName
ALTER DATABASE MyDatabaseName SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(MyDatabaseName_Log, 1)
ALTER DATABASE MyDatabaseName SET RECOVERY FULL WITH NO_WAIT
GO


Comments

Guest Author: Me08 Feb 2013

The reason is that in almost all circumstances you should avoid doing it. Doing so breaks the transaction log chain, which makes recovering to a point in time impossible because you have lost transactions that have occurred not only since the last transaction log backup but will not able to recovery any future transactions that occur until a differential or full database backup has been created. This method is so discouraged that Microsoft is not including it in SQL Server 2008 and future versions of the product. I’ll include the syntax here to be thorough, but you should try to avoid using it at all costs.

If your database is on full recovery mode and you have done any backup of your log file, chances are that your backups are irrelevant. You should consider change our recovery to SIMPLE and let the SQL engine handle the log file.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: