Many newcomers (and even experienced developers who never had to manage their own SQL Servers) usually don’t understand the difference between Full Recovery mode and Simple Recovery mode, and more than that they don’t know how to manage their SQL databases so that their LDF files won’t grow indefinitely.

Full Recovery vs Simple Recovery

Simple Recovery uses transaction log only for uncommited transactions, so this file is very small, and doesn’t grow forever (allocated space is reused), so there is no Log backup for Simple Recovery.

Full Recovery tracks all past transactions in the transaction log, so log grows forever unless we do maintenance .

The major advantage of Full Recovery is that it allows us to use point in time restore, which allows to restore to an exact time, reverting (or inspecting) pontual database changes.

I strongly recommend Full recovery for any serious production environment.

Ola Hallengren Maintenance Scripts

Ola Hallengren is an experienced DBA who has developed these amazing SQL Server Maintenance scripts, comprised of scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server.

Download the scripts here, run then on your server, and it will install many useful stored procedures into your master database.

I strongly recommend those scripts so that you don’t need to reinvent the wheel.

Backup Databases

The following script will backup all your user databases (applies both to Full Recovery or Simple Recovery)

-- Full backup (keeping 7 last days)
EXECUTE master.dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'N',
@CheckSum = 'Y',
@CleanupTime = 168 -- 168hs =  7-day retention

OBS: “FULL” is for “FULL backup” (as opposed to DIFFERENTIAL backup), and has NOTHING to do with “Full Recovery”.
OBS: USER_DATABASES can be replaced by a single database

Backup Transaction Log

The following script will backup transaction log for your user databases (applies only to Full Recovery)

We should periodically backup the transaction log, so that it doesn’t grow indefinitely. Backing up the transaction log does NOT automatic shrink the transaction log, but ALLOWS us to shrink that log because SQL can only consider part of the transaction log as “unused space” if the respective transactions were backed up before.

-- Transaction Log Backup
EXECUTE master.dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

OBS: Simple Recovery uses transaction log only for uncommited transactions, so there is no Log backup for Simple Recovery
OBS: USER_DATABASES can be replaced by a single database

Switching from Full to Simple Recovery

ALTER DATABASE yourdb SET RECOVERY SIMPLE WITH NO_WAIT;

OBS: The transaction log won’t shrink automatically! See below.

Switching from Simple to Full Recovery:

ALTER DATABASE yourdb SET RECOVERY FULL WITH NO_WAIT;

Shrink the Transaction Log file (LDF)

If the database was in Full Recovery and didn’t had log maintenance, probably the log file became huge.

Shrink the Transaction Log for a database in Simple Recovery Mode

If the database is running in Simple Recovery, you can shrink easily:

-- Shrink the file
USE yourdb;
DBCC SHRINKFILE(yourdb_log, 10); -- 10MB should be enough for us

OBS: yourdb_log is the LOGICAL NAME of the log file, and is not necessarily the same name as the physical file in disk, specially if your database was renamed or restored from a backup with different name.
e.g. if we create a database “Northwind” and later rename or restore into a database “Northwind2012” , the internal name of your log file will be Northwind_log, even if the physical file is called Northwind2012_log.ldf or whatever.
To obtain the logical name of your log file, use this command:
SELECT name FROM sys.database_files where type_desc='LOG'

Shrink the Transaction Log for a database in Full Recovery Mode

If the database is running in Full Recovery, shrinking may be harder. First of all, note that the transaction log has a pointer that indicates which part of the log file was backed up and which part wasn’t backed up. Shrinking can only remove parts which were backup up, so first of all, make a transaction log backup (see above Backup Transaction Log).

To shrink the transaction log, you should use the same shrinking command as used for Simple Recovery (above), however, it’s common that we get an annoying error where the log doesn’t shrink and you get an error Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.

To prevent/avoid this problem, just backup the transaction log of your database and shrink the log file immediately after:

use master;
EXECUTE master.dbo.DatabaseBackup
@Databases = 'yourdb',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y';

USE yourdb;
DBCC SHRINKFILE(yourdb_log, 200);

If the transaction log is already too large and you can’t even backup, just truncate the log file by temporarily changing to simple recovery mode:

ALTER DATABASE yourdb SET RECOVERY SIMPLE WITH NO_WAIT;

USE yourdb;
DBCC SHRINKFILE(yourdb_log, 200);

ALTER DATABASE yourdb SET RECOVERY FULL WITH NO_WAIT;

Finally, after shrinking the file, configure it for auto-growth:

USE [master];
GO

ALTER DATABASE yourdb
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 300MB, FILEGROWTH = 50MB);
GO

Maintenance Jobs

Based on the script’s author suggestion, this my suggested schedule for your servers:

User databases:

• Integrity check (weekly)

  EXECUTE dbo.DatabaseIntegrityCheck
  @Databases = 'USER_DATABASES',
  @CheckCommands = 'CHECKDB',
  @PhysicalOnly = 'Y'
  

• Index & Statistics maintenance (weekly)

  EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y' 
  

• Full backup (daily)

  EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'C:\Backup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 168 -- 168hs =  7-day retention
  

• Transaction log (daily or hourly [or even more frequent] if we want to set up database mirroring)

  EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'C:\Backup',
  @BackupType = 'LOG',
  @ChangeBackupType = 'Y'
  

System databases:

• Full backup (daily)
• Integrity check (daily)

Server:

• sp_delete_backuphistory (weekly)
• sp_purge_jobhistory (weekly)
• CommandLog cleanup (weekly)
• Output file cleanup (weekly)

TO DO: use xp_cmdshell to zip backups, because SQL Express can’t compress backups

TO DO: modify DatabaseBackup script to automatically shrink log file immediately after a LOG backup.

On this post I just wanted to make a cheat sheet and scratch the surface. If you want to gain a deep understanding of Transaction Log backup and shrinking, please refer to this answer and the references linked there.