SQL Server: Shrink Transaction Logs

Contents

  1. Script
  2. References
 

Script

-- Backup database:
USE master;
BACKUP DATABASE dbname  TO DISK = 'D:\Backup\dbnameFull.bak' WITH FORMAT;   
GO
 
-- Backup transaction log:
BACKUP LOG dbname TO DISK = 'D:\Backup\dbnameLog.TRN'
GO
 
-- Set recovery mode to SIMPLE:
USE master;
ALTER DATABASE dbname SET RECOVERY SIMPLE;
GO
 
-- Shrink transaction log file to 1mb:
USE dbname;
GO
DBCC SHRINKFILE(2, 1)
GO
 
-- Set recovery mode to FULL if needed:
USE master;
ALTER DATABASE dbname SET RECOVERY FULL;
GO

References

* https://community.spiceworks.com/topic/276136-how-to-safely-delete-archive-ldf-files
* Recovery Models (SQL Server)

This entry was posted in SqlServer. Bookmark the permalink.