Back up your database first.
Launch SQL Server Management Studio. Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)
Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)
Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:
DBCC SHRINKFILE(<log_file_name_Log>) BACKUP LOG <database> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<log_file_name_Log>)
A little more information :
The ‘proper’ thing to do these days is to put the database into ‘simple recovery’ and then to shrink the log.
A few commands that I think might do it :
ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE DBCC SHRINKFILE(<log_file_name_Log>) ALTER DATABASE [mydatabase] SET RECOVERY FULL
Some notes on SQL Server backups :
- transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
- a full backup alone will not do the trick – that does not touch the transaction log
- transaction logs must be backed up via ‘BACKUP LOG’
- if the dba does not require transaction log backups, it is advisable to move to Simple Recovery