How do I truncate a SQL server log file?
Truncate the transaction log
- Right-click the database and select Properties -> Options.
- Set the recovery model to Simple and exit the menu.
- Right-click the database again and select Tasks -> Shrink -> Files.
- Change the type to Log .
- Under Shrink action, select Reorganize pages before releasing unused space and click OK.
What is log truncate?
Log truncation is a SQL Server feature that enables disk space reserved for logs to be re-used. The transaction log consists of one or more log files. A log file contains log records including undo and redo information. The undo information is necessary for rolling back open database transactions.
How is the log truncation honored in SQL Server?
In SQL Server 2000 and up, SQL Server has a concept of recovery model that defines how the log truncation is honored e.g. In simple recovery – only a checkpoint will truncate the log where as in full recovery mode – only a log backup will truncate the log.
What is truncate log on checkpoint in Tivoli?
Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint.
How is checkpoint truncate in simple recovery mode?
In simple recovery – only a checkpoint will truncate the log where as in full recovery mode – only a log backup will truncate the log. If you want to understand more about Transaction Log Management then refer to Stairway to Transaction Log Management
Where to find trunc.log in SQL Server 2008R2?
You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure ‘sp_dboption’.