如何解决镜像1412错误以及如何删除原有的镜像配置

[lilinew]

环境都是windows server2008
2台机器分别装有sqlserver2008 域环境下
配置2个sqlsever(gm4主体 gm3镜像)总是提示以下错误
or not The remote copy of database has not been rolled forward to a point in time that is encompassed in the loacl copy 1412
（我是用sqlserver的工具来配置镜像的 。 主体上做备份 之后在镜像机器上恢复。之后在主体机器上做镜像配置）（这2台机器以前和另外1台机器 3台组成一个主体gm3 镜像gm2 见证gm1的镜像环境。后来其中一台机器gm2给移除 换了新的机器gm4 ）
在线环境，主体机器的库在不停的插入记录

这里有疑问
1 如何解决这个主体机器错误or not The remote copy of database has not been rolled forward to a point in time that is encompassed in the loacl copy 1412。 在线环境，gm3主体在不停的插入新数据的。在2台机器备份恢复配置的间隙新插入的记录对镜像配置有影响（新插入的记录产生日志导致or not The remote copy of database has not been rolled forward to a point in time that is encompassed in the loacl copy 1412） ？
2 logship 可以删除从视图原有的配置
那么镜像如何 删除原有的配置 （我能netstat -aon | findstr "5022"） 跟踪发现老配置（已经移除）的gm2 甚至还有tcp的建立的 。 （gm2移除是断电的时候的处理所以没有进行过规范处理）

[lilinew]

我在主体gm3新建立一个库， 之后备份恢复在 镜像gm4机器
建立镜像是成功的

初步判断是 gm3在线的主体库 更新数据并发情况过于复杂导致lsn记录 一个在15分内数据多而且难以描述
（仅仅是推理）
现在的想法 不通过停止iis的应用来解决这个并发在线库的建立镜像问题？

[唐诗三百首]

主体,镜像,见证都删除后重建ENDPOINT试试..

[lilinew]

感谢参与
归根到底还是日志原因 非其他原因
首先判断select backup_set_id, first_lsn , last_lsn,name,database_name,backup_start_date,backup_finish_date ,backup_size from dbo.backupset
之后参考 。几篇百度的重要mirror文档。 发现sqlserver深入的书市面买不到。只有英文的。
http://www.youtube.com/watch?v=x1B3liOlavU&feature=related
http://www.youtube.com/watch?v=YOLqyPa3LOw
http://www.mssqltips.com/tip.asp?tip=1225
How to determine SQL Server database transaction log usage
By: Greg Robidoux | Comments (0) | Print | Share4

Expand your SQL Server horizons with a Kindle loaded with 5 eBooks

Problem
One crucial aspect of all databases is the transaction log. The transaction log is used to write all transactions prior to committing the data to the data file. In some circumstances the transaction logs can get quite large and not knowing what is in the transaction log or how much space is being used can become a problem. So how to you determine how much of the transaction log is being used and what portions are being used?

Solution
In most databases the transaction log is generally just one (ldf) file, but inside the overall transaction log is a series of virtual log files as depicted below.

source (SQL Server 2005 Books Online)

The way the transaction log is used is that each virtual log file is written to and when the data is committed and a checkpoint occurs the space becomes useable again. Although this does depend on your database recovery model, whether you are using replication and your backup processing. If there are no additional virtual logs available, SQL Server will grow the transaction log, based on your database settings, to accommodate the additional space that is required.

source (SQL Server 2005 Books Online)

The use of the file and the virtual logs all depends on how the database is used and other settings you have enabled in your database. If you are publishing data from this database or if the database is set to the the Full or Bulk-Logged recovery mode, this will also affect whether the process loops back to the beginning of the file, if it uses the next available virtual log or it if needs to grow the transaction log and create additional virtual logs.

DBCC SQLPERF(logspace)
One command that is extremely helpful in understanding how much of the transaction log is being used is DBCC SQLPERF(logspace). This one command will give you details about the current size of all of your database transaction logs as well as the percent currently in use. Running this command on a periodic basis will give you a good idea of how the transaction logs are being used and also give you an idea on how large they should really be. This is a question that is often asked by a lot of people that use SQL Server and as you run this you will find out there is no perfect answer it all depends on a lot of criteria such as:

*

recovery model
*

size of the transactions
*

how large your tables are and therefore how much space is needed for index maintenance
*

how frequently you run transaction log backups
*

whether the database is published or not
*

etc...

To run this command issue the following in a query window:
DBCC SQLPERF(logspace)

This is sample output:



From here we can see the size of the transaction logs as well as how much space is being used. The current log space used will tell you how much of the transaction log is being used. If this percentage is high and the size of the log is quite big it is probably due to one of the items listed above.

DBCC LOGINFO
The next command to look at is DBCC LOGINFO. This will give you information about your virtual logs inside your transaction log. The primary thing to look at here is the Status column. Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of "2" is in the output. This will tell you what portions of the log are in use and which are not in use Status = 0. Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log. If you keep running this command as you are issuing transactions you will see these numbers keep changing.

To run this command issue the following in a query window:
DBCC LOGINFO

This is sample output:

If we now run a transaction log backup such as the following:
BACKUP LOG DBUtil WITH NO_LOG

or

BACKUP LOG DBUtil TO DISK = 'C:\Backup\DBUtil.trn'

and then rerun the command you will see how the Status=2 has changed in the file. The last entry is still marked as in use, but the previous entries have been reset to 0.

One thing to note, if you do run BACKUP LOG...WITH NO_LOG you will need to run another full backup, otherwise SQL Server will just reuse the space in the transaction log because there is no way to restore the next transaction log backup since you did not do a real transaction log backup and therefore the settings in the log file were reset. Also, if you don't have a full backup of your database the space in the transaction log also gets reused. This is because there is no full backup to restore first and therefore you can not issue a transaction log restore.

DBCC OPENTRAN
Another command to look at is DBCC OPENTRAN. This will show you if you have any open transactions in your transaction log that have not completed or have not been committed. These may be active transactions or transactions that for some reason never completed. This can provide additional information as to why your transaction log is so big or why you may not be able to shrink the transaction log file. This will show you both open transactions as well any un-replicated transactions if the database is published.

To run this command issue the following in a query window:
DBCC OPENTRAN

This is sample output:



Now that you have an idea how much of your transaction log is being used and what is being used you can start to make some decisions on how large the transaction log should be. One thing you should try to do is find that optimum size in order to eliminate having to shrink and grow the transaction log on a constant basis. As with all database and server activity it is best to minimize the overhead as much as you can and this is one of those areas that you can somewhat manage by creating and maintaining the optimum transaction log size.

Next Steps

* Make sure you are using the correct backup strategy based on your recovery model
* If you are going to use BACKUP LOG...WITH NO_LOG you should just look at changing your database to the Simple recovery model
* If you do need to shrink your transaction log file take a look at DBCC SHRINKFILE.
* Here are some other tips regarding transaction log space.
o Monitoring transaction log space
o Managing SQL Server 2000 Transaction Log Growth
困扰大几天 终于坚持下来了

[lilinew]

远程“AdventureWorks”数据库没有前滚到本地数据库日志副本中包含的一个时间点。 （Microsoft SQL SERVER， 错误：1412）
本地测试没有问题
搞了半天又是1478的错误 可以肯定是事务日志问题

（之前做的是全备份）
如何把最新的事务日志传给镜像机器 ？
2边语句该如何写？

[Pro_w]

恭喜楼主

[GoAwayZ]

[Quote=引用楼主 liyihongcug 的回复:]
环境都是windows server2008
2台机器分别装有sqlserver2008 域环境下
配置2个sqlsever(gm4主体 gm3镜像)总是提示以下错误
or not The remote copy of database has not been rolled forward to a point in time that is encompassed in the loa……
[/Quote]帮顶。