How to truncate SQL Server Transaction Log

Step 1.  Find Size of Database Log files

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc

Step 2.  See if databases have active transactions . You won’t able to truncate log easily with active transactions on database.

select name,log_reuse_wait_desc from sys.databases

name    log_reuse_wait_desc
master    NOTHING
tempdb    ACTIVE_TRANSACTION
model    NOTHING

Step 3. Locate active transaction details . If needed kill sessions returned by dbcc opentran.

DBCC OPENTRAN

Transaction information for database ‘Test’.

Oldest active transaction:
SPID (server process ID): 79
UID (user ID) : -1
Name          : BULK INSERT
LSN           : (63361:51900:6)
Start time    : Aug 16 2013  7:42:53:783AM
SID           : 0x978700b4443f8e489fa13f97b3f375fc
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Step 4.  TSQL to Shrink Transaction log from all databases. To truncate log from specific databases alter NOT IN condition.

DECLARE @dbname VARCHAR(100)
DECLARE @dbid INT
DECLARE curDatabases CURSOR FOR
SELECT name, database_id
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’)
OPEN curDatabases
FETCH NEXT FROM curDatabases INTO
@dbname,
@dbid
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
DECLARE @dbfilename VARCHAR(100)
PRINT ‘USE MASTER’
PRINT ‘GO’
PRINT ‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE’
PRINT ‘GO’
PRINT ‘USE [‘ + @dbname + ‘]’
PRINT ‘GO’
Select @dbfilename = name
FROM sys.master_files
WHERE type = 1
AND database_id = @dbid
PRINT ‘DBCC SHRINKFILE (‘ + @dbfilename + ‘, 1024)’
PRINT ‘GO’
PRINT ”
END
FETCH NEXT FROM curDatabases INTO
@dbname,
@dbid
END
CLOSE curDatabases
DEALLOCATE curDatabases