How do you clear the SQL Server transaction log?

Question

I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?

Answer 1

To my experience on most SQL Servers there is no backup of the transaction log. Full backups or differential backups are common practice, but transaction log backups are really seldom. So the transaction log file grows forever (until the disk is full). In this case the recovery model should be set to "simple". Don't forget to modify the system databases "model" and "tempdb", too.

A backup of the database "tempdb" makes no sense, so the recovery model of this db should always be "simple".

Answer 2

Try this:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

Answer 3

It happened with me where the database log file was of 28 GBs.

What can you do to reduce this? Actually, log files are those file data which the SQL server keeps when an transaction has taken place. For a transaction to process SQL server allocates pages for the same. But after the completion of the transaction, these are not released suddenly hoping that there may be a transaction coming like the same one. This holds up the space.

Step 1: First Run this command in the database query explored checkpoint

Step 2: Right click on the database Task> Back up Select back up type as Transaction Log Add a destination address and file name to keep the backup data (.bak)

Repeat this step again and at this time give another file name

Step 3: Now go to the database Right-click on the database

Tasks> Shrinks> Files Choose File type as Log Shrink action as release unused space

Step 4:

Check your log file normally in SQL 2014 this can be found at

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014EXPRESS\MSSQL\DATA

In my case, its reduced from 28 GB to 1 MB