1413 error when starting Database Mirroring – How many virtual log files is too many?
Recently in Cumulative update package 6 for SQL Server 2005 Service Pack 2 we released a fix for a problem where you receive a 1413 error when starting database mirroring. The error that you get in the looks like this:
Error: 1413, Severity: 16, State: 1.
Communications to the remote server instance 'TCP://ComputerName:PortNumber' failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started.
In addition to this, if you look in the error logs themselves you will normally see the following 2 errors reported as well:
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database 'xxx'. This is an informational message only. No user action is required.
And you also note:
Error: 1479, Severity: 16, State: 1.
The mirroring connection to "TCP://ComputerName:PortNumber" has timed out for database "xxx" after 10 seconds without a response. Check the service and network connections.
Although you can install the hotfix, I think it’s worth considering why this problem actually occurs and how you can potentially avoid it happening at all through good database management practices, which have benefits far and beyond the configuration of database mirroring.
Hopefully you are familiar with the architecture of a SQL Server transaction log file. In principal the primary reason that this 1413 and its associated time out errors occur is that when you start database mirroring SQL Server needs to read through the transaction log file to bring the 2 databases into a synchronized state, following the restore of the last transaction log backup to the mirror partner. The time is takes to do this is dependent upon the number of virtual log files contained within the physical log file. This sometimes leads to confusion as a relatively small physical log file can contain thousands of virtual log files if it’s been badly managed. This in turn means that a relatively small physical log file (say 1GB) can be the cause of this 1413 error, and in turn the exact same database configured for mirroring on the exact same hardware will successfully set up when it has an identically sized physical log, but one that has been better managed and doesn’t contain so many virtual log files.
To demonstrate this, try the following reproduction examples. To run these you’ll need 2 SQL Server instances with mirroring endpoints already set up (I’ll assume if you’re reading this far that you already understand this). A good place to try this repro is a test server environment where you already have a mirroring partnership configured. The repros use the undocumented DBCC LOGINFO command to examine the number of virtual log files in the physical log. Remember as with all undocumented commands, use of it is not supported, and you use it at your own risk.
The example uses a common scenario where a log has been left to grow and runs out of space, but actually there are many different ways that you can cause your log to grow. Some are documented here. This is just a quick way to run the demo.
Script 1: produces the 1413 failure – note the thousands of VLFs
--CONNECT to the primary server insatnce
use master
go
drop database vlf --(if you need to)
--create a database on the root of C with a small starting log size
--which then grows in small increments
create database vlf
on (name=vlf, filename='c:\vlf.mdf')
log on (name=vlf_log, filename='c:\vlf_log.ldf',size=1MB,maxsize=1GB,filegrowth=1MB)
go
use vlf
go
--create a dummy table to fill up
create table vlf_test
(
a int identity(1,1) not null,
b char(8000) not null,
c int not null
)
go
insert vlf_test values ('vlftest',0)
go
--create a starting point of backups
backup database vlf to disk = 'c:\vlf.bak' with stats = 1
go
backup log vlf to disk = 'c:\vlf1.bak'
go
set nocount on
go
--grow the log until it runs out of space
declare @x int
declare @a char(8000)
declare @b char(8000)
set @a = replicate('a',8000)
set @b = replicate('b',8000)
set @x = 1
while 1=1
begin
update vlf_test set b = @b, c = @x where a = 1
update vlf_test set b = @a where a = 1
set @x = @x + 2
end
go
--now alter the log size and backup the log
alter database vlf modify file (name=vlf_log, maxsize=2GB)
go
backup log [vlf] to disk = 'c:\vlf2.bak' with stats=1
go
--use the undocumented DBCC LOGINFO command to check the number of VLFs in our
dbcc loginfo (vlf)
go
--note the number of VLFs, it should be several thousand
--for me it was 4100
--connect to the other server which you wish to be the mirror
--copy the 3 backup files across to it's C drive and run the following
use master
go
restore database [vlf] from disk = 'c:\vlf.bak' with norecovery, replace, file=1,
move 'vlf' to 'c:\vlf.mdf',
move 'vlf_log' to 'c:\vlf_log.LDF'
go
restore log [vlf] from disk = 'c:\vlf1.bak' with norecovery
go
restore log [vlf] from disk = 'c:\vlf2.bak' with norecovery, stats=1
go
--you now have a pair of databases ready to have mirroring started
--try to start database mirroring using the SSMS GUI and you will receive the 1413 error
--the only exception to this might be if you run it on particularly fast hardware
Script 2: works fine - identical except that the log has been pre-grown to the same size. This is good practice for all database files, whatever their type.
--CONNECT to the primary server instance
--remember to delete any old backup files of the same name from previous tests
use master
go
drop database vlf --(if you need to)
create database vlf
on (name=vlf, filename='c:\vlf.mdf')
log on (name=vlf_log, filename='c:\vlf_log.ldf',size=1GB,maxsize=1GB,filegrowth=250MB)
go
use vlf
go
create table vlf_test
(
a int identity(1,1) not null,
b char(8000) not null,
c int not null
)
go
insert vlf_test values ('vlftest',0)
go
backup database vlf to disk = 'c:\vlf.bak' with stats = 1
go
backup log vlf to disk = 'c:\vlf1.bak'
go
set nocount on
go
--this will grow the log until it runs out of space
declare @x int
declare @a char(8000)
declare @b char(8000)
set @a = replicate('a',8000)
set @b = replicate('b',8000)
set @x = 1
while 1=1
begin
update vlf_test set b = @b, c = @x where a = 1
update vlf_test set b = @a where a = 1
set @x = @x + 2
end
go
--now alter the log size and backup the log
alter database vlf modify file (name=vlf_log, maxsize=2GB)
go
backup log [vlf] to disk = 'c:\vlf2.bak' with stats=1
go
--use the undocumented DBCC LOGINFO command to check the number of VLFs in our
dbcc loginfo (vlf)
go
--note the number of VLFs, it should be only about 16 or so
--connect to the other server which you wish to be the mirror
--copy the 3 back files across to it's C drive
restore database [vlf] from disk = 'c:\vlf.bak' with norecovery, replace, file=1,
move 'vlf' to 'c:\vlf.mdf',
move 'vlf_log' to 'c:\vlf_log.LDF'
go
restore log [vlf] from disk = 'c:\vlf1.bak' with norecovery
go
restore log [vlf] from disk = 'c:\vlf2.bak' with norecovery, stats=1
go
--you now have a pair of databases ready to have mirroring started
--try to start database mirroring using the SSMS GUI and it should work
--the only exception to this might be if you run it on particularly slow hardware
I ran my test on 2 small single processor servers. You might get slightly different results as the scripts note, depending upon your exact configuration. I used SQL Server 2005 build 3186 (CU#3) for this test.
My main point here is that you should look to control your transaction log proactively. There are obviously times when this is not possible, or other mitigating circumstances arise, which is why we have released a hotfix to improve the speed with which the database mirroring configuration reads the log files. This improvement is also included in SQL Server 2008 as well. However there are other issues that you can encounter if you let you virtual log files grow unchecked, such as excessive recovery times, so as with all SQL Server file management tasks, proactive is best. We always recommend that our customers analyze their expected usage and pre-grow their database files.
I hope this helps to explain the database mirroring error in more depth, but feel free to post a comment if you have a question.
Comments
- Anonymous
November 25, 2009
Hi thought I'd share the script I use to check the VLFs for all databases on the server. if object_id('tempdb..#r') is not null drop table #r if object_id('tempdb..#t') is not null drop table #t create table #r (dbname sysname, countVLFs bigint) create table #t (a bigint,b bigint, c bigint, d bigint, e bigint, f int, g varchar(50)) exec sp_msforeachdb "truncate table #t; use [?]; insert #t exec ('dbcc loginfo'); insert #r select '?', count(*) from #t" select * from #r