How to truncate Mirrored Database Log File
Mirroring is a new feature comes with SQLServer 2005.
Mirroring provides High Availibity of databases without any dataloss.
Mirroring Comes with three flavours
(1) High Performance (asynchronous).
(2) High safety without automatic failover (synchronous).
(3) High safety with automatic failover(synchronous) need withness server.
(Read more in BOL Look For : mirroring databases [SQL Server])
This document will focus on how to shrink log file while databases are participating in mirroring.
Database files participating in mirroring can’t be shirnked by using truncate_only option with Backup log command.
To shrink Log file of database participating in mirroring, Backup the log file at any location (can be a local drive or network location)
BackupLog <DatabaseName> to disk=‘D:\ff\dbname.trn’
Lets check if all transaction is written to disk.
DBCC LOGINFO(‘database name’)
Transaction which are written on disk will have status=0
In case if last transaction show status=2 then backup the database log once again.
Now shrink the file
DBCC SHRINKFILE(Logfileid)
or
DBCC SHRINKFILE(Filename,minsize)
The file will shrink to its maximum extent.
msufian
Do I have to remove mirroring before shrinking the log
files ?
You don’t have to remove DB mirroring.
Awesome… really great matter. I am goin to write about it as well!
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)
Here is SQL script we use to backup all our database. We use it with SQL job:
DECLARE @sql VARCHAR(MAX)
SET @sql = ”
SELECT @sql = @sql+
‘
BACKUP DATABASE [' + name + '] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘.bak”’
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)
Here is little VBS script we use to delete old backups of databases and transaction log backups. We use SQL job (cmdexec type command) to run daily
cscript /nologo \\CLOUD\Root\Databases\DeleteOldDatabases.vbs
Here is VBS script to run:
‘Start
Option Explicit
on error resume next
Dim oFSO
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
Dim strExtension
‘Definitions
iDaysOld = 7
strExtension = “.bak”
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
set oFolder = oFSO.GetFolder(“\\CLOUD\Root\Databases\”)
set oFileCollection = oFolder.Files
‘Walk through each file in this folder collection.
For each oFile in oFileCollection
‘msgbox (Now()- iDaysOld),vbOKOnly
If oFile.DateLastModified < (Now() – iDaysOld) Then
If (strExtension = "") Or (Right(UCase(oFile.Name), Len(strExtension)) = UCase(strExtension)) Then
oFile.Delete(True)
End If
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
'End
All 3 codes work together. At 2AM we run transaction backup, at 3AM we backup databases and at 4 AM we delete old backup files that is older than 7 day. You can adjust your time frame using VBS script. Everything is working on production using SQL mirroring.
I hope this 3 scripts can save some people time and energy. I personally spent a lot of time finding right answers and most of people don’t know what they are talking. I never able to find WORKING examples. So here you go, enjoy. If you find them useful please let me know or leave backlink to our company site.
Good Info.
Thanks..!!