The following script loops through all the Tlogs and attempts to shrink them. Remember that a Tlog can be shrunk only if there is empty space at the end. In order to tell where the empty space is, run:
use [database] DBCC LOGINFO
If the Status column is 0, then it's empty. If you do not have empty space at the end, it will not shrink, so you need to run Tlog backups. Sometimes you need to run Tlog backups several times for the portion that's empty to move to the end of the file. More on that here:
Now, for the script:
/*
Get a list of Databases and its Tlogs, and shrink them
Do the following.
1- Run Tlog Backups
2- Run this script
Author: Miguel Quintana
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012
Does not work:
MSSQL2000
*/
DECLARE @numRec INT
DECLARE @counter INT
DECLARE @DBName SYSNAME
DECLARE @Tlog SYSNAME
DECLARE @CMD NVARCHAR(MAX)
DECLARE @table TABLE
(RowID INT IDENTITY(1, 1), dbname SYSNAME, tlogname SYSNAME)
-- create a table with the DBname and its Tlog file
-- skip offline and system DBs
INSERT INTO @table
SELECT db_name(database_id), name FROM sys.master_files
WHERE type_desc = 'LOG' and state_desc ='ONLINE' and database_id > 4
-- Set and initialize counters
SELECT @numRec=count(*), @counter=1 FROM @table
WHILE @counter <= @NumRec
BEGIN
SELECT @DBName = dbname, @Tlog = tlogname FROM @table WHERE RowID = @counter
SET @CMD='USE ' + quotename(@DBName, N'[') +
N'
CHECKPOINT
DBCC SHRINKFILE (['+@Tlog+'], 0, TRUNCATEONLY)'
print @CMD
-- EXECUTE (@CMD)
set @counter=@counter+1
END
Ok.. well… what happen if you are forced to shrink the Tlog and you don't have space for backups? Now what?
There is a way where you can take a backup "into the ether"… tricking the database into dumping all pages and forcing to mark the Tlog as empty pages.
Doing this will not cause data loss, however it will invalidate the backup chain, and a full backup should be taken as soon as possible.
BACKUP LOG [AdventureWorks2012] TO DISK = N'NUL'