Shrink or truncate Mirrored Database Log File in SQL Server 2005


Here is script to truncate all your database. We run it using SQL Server Agent job. Works like a charm. We use it to truncate transaction logs when database running with SQL mirroring.

——————————————————————————————————————–

DECLARE @sql VARCHAR(MAX)
SET @sql = ”
SELECT @sql = @sql+

USE [‘ + name + ‘]
BACKUP LOG [‘ + name + ‘] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘_log.bak”
DBCC SHRINKFILE (”’ + name + ‘_log”, 1, TRUNCATEONLY)’
FROM sys.databases
WHERE name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘pubs’, ‘Northwind’, ‘ReportServer$SQL2005′, ‘ReportServer$SQL2005TempDB’)
AND user_access_desc = ‘MULTI_USER’
AND is_read_only = 0
AND state_desc = ‘ONLINE’

–PRINT @sql
EXEC(@sql)

———————————————————————————————————-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: