Growing Log file issues - How to shrink a SQL server Log and data file.
Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file.
Before doing this backup your databases using Management studioOpen 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:
ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
ALTER DATABASE [mydatabase] SET RECOVERY FULL
The following screen shows the log file before the shrink.
Log file after shrink
Points of interest
- 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: