To shrink the transactional logs in SQL Server 2005 follow this link:
https://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/
In SQL Server 2008 it’s a matter of changing the recovery model to SIMPLE and re-running the DBCC SHRINKFILE command – assuming the current recovery model is FULL.
Firstly, here is a quick script to backup the database and also the transactional log (if required to back up separately) to disk:
— QUICK SCRIPT TO BACKUP THE DATABASE TO DISK
BACKUP DATABASE [DATABASE]
TO DISK = ‘c:\backup\DATABASE.BAK’
WITH NOFORMAT, NOINIT, NAME = ‘DATABASE-Full Database Backup’,
SKIP,NOREWIND,NOUNLOAD,STATS = 10— QUICK SCRIPT TO BACKUP THE LOG FILE TO DISK
BACKUP LOG [DATABASE]
TO DISK = ‘c:\backup\DATABASE_LOG.BAK’
WITH NOFORMAT, NOINIT, NAME = ‘DATABASE-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
Step 1: View the current recovery model configuration.
— SHOW AVAILABLE DATABASES AND THEIR CURRENT RECOVERY MODEL CONFIGURATION
select name,recovery_model_desc from sys.databases
Step 2: Change the recovery model to SIMPLE.
–CHANGE THE RECOVERY MODEL CONFIGURATION TO SIMPLE
Alter database [DATABASE] set Recovery simple
Step 3: Use DBCC SHRINKFILE to truncate the transactional logs of the database.
— USE BDCC CHRINK FILE TO TRUNCATE THE TRANSACTIONAL LOGS
USE [DATABASE]
GO
DBCC SHRINKFILE ([DATABASE_LOG], 0, TRUNCATEONLY)
GO
Step 4: Return the recovery model back to FULL if required.
— CHANGE TEH RECPVERY MODEL BACK TO FULL IF REQUIRED
Alter database [DATABASE] set Recovery full