Here’s a quick screencast that demonstrates how to change the recovery model for your database and shrink the database logs using SQL Server Management Studio:
Back up your database!
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>)
Afterwards, perform a full backup of the database.
The file should shrink to a ridiculously small shadow of its former self.
Edit: A little more info from a customer.
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 FULLSome 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
A little more info here:
