Replication failed

Question

Replication failed

Ed7

SSCrazy Eights

Points: 8516
More actions
January 5, 2007 at 10:12 pm

#171762
Hi, I am trying to setup the transactional replication between Server A and Server B cross WAN networks.
Server A is running SQL Server 2005 and Server B is running SQL Server 2000.
During initial creation of publication from Server A to replicate the Server B. I run into a problems.
The Server B SQL Server 2000 log file will keep incrasing until the disk is full and causing the publication to fail.
I run the following scripts before perform the replications:
- BACKUP LOG swdatabase WITH TRUNCATE_ONLY
- DBCC SHRINKFILE (swdatabase_log, 200)
Please kindly helps!!! Thank you.
Here are the errors:
- Adding article 103 of 103 (Error)
Messages
· SQL Server Management Studio could not create article 'Data'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The log file for database 'Sweetwater' is full. Back up the transaction log for the database to free up some log space.
Changed database context to 'Sweetwater'.
The statement has been terminated. (Microsoft SQL Server, Error: 9002)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476

· SQL Server Management Studio could not create article 'DataToCopy'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The log file for database 'Sweetwater' is full. Back up the transaction log for the database to free up some log space.
Changed database context to 'Sweetwater'. (Microsoft SQL Server, Error: 9002)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476

SQL Server Management Studio could not create article 'Device'. (New Publication Wizard)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

What sql build you are using? and what is edition you are using?

http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_thread/thread/ab1d58a7ffa7777b/8fc8657d58702419?lnk=st&q=SQL+Server+Management+Studio+could+not+create+article&rnum=1#8fc8657d58702419

A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

http://support.microsoft.com/kb/317375

MohammedU
Microsoft SQL Server MVP

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 2

The SQL Server 2000 version 5.2 and build 3790 with SP1.

Thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Hi Edwin,

That is the O/S version. Win2003 SP1. Mohammed is asking for the SQL server version.

Do a select @@version from Query analyzer on the servers and report the results.

IIRC, when the distributor applies the snapshot to the subscriber, the data gets copied, then the indexes are built on the subscriber. This can take a lot of log space. You may need to backup the log several times on the subscriber during the initial phase to make more space available.

Shrinking it will only slow things down, as it will need to grow in size again. There may be other problems related to the SP level on the servers, so get the versions.

jg

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 4

Microsoft SQL Server 2000 - 8.00.194 (Intel X86).

Thank you JG.

Edwin

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Edwin, build 194 is service pack *none*. I'm not sure that is the problem, and perhaps Mr. Uddin has something specific in mind, but I would think you should be using at least SP3 or later. I can't imagine anything working right without a service pack. (heh)

Also what is the size of your server B data files and log files (at the point of failure)

jg

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 6

The Server B database currently set Recovery mode to be SIMPLE!!!!

Did it cause the log file keep increasing?

Thank you.

Ed

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 7

Hi Jeff,

Thank you for your response. I did not intially setup the database. It was setup by our Senior DBA.

Agreed!! that the SQL Server 2000 at least should apply SP3. However, it currently hold custom application databases.

Not sure SP3 will cause issues to custome applicatioins.

Best regards,

Ed

Jeff Gray SSChampion Points: 10667 More actions · Answer 8

Okey doke. Perhaps the latest SP is not the answer. Hopefully it is not.

Now, what was the size of the data file(s) and the size of the trans. log file(s) on the subscriber when the disk was full?

Simple model will automatically truncate the trans. log when a checkpoint occurs, so it should be acceptable to leave that at simple, unless there's a SP related problem.

Now your message says that the error occured on article 103 of 103, which means to me that it probably finished the data, and just shows the last completed step of the snapshot. So what Iam thinking is that the distributor is failing while creating the indexes at the subscriber. On a large publication, it can appear that the snapshot is almost finished becuase the status shows X out of X articles completed, when in reality much more time is required because the indexes are being created.

What I'm not sure about is if the entire snapshot is created inside an isolated transaction. If it is, then truncation will not help, because the entire transaction would have to be completed before it could be marked as inactive in the trans log.

Indexing or reindexing a database can make the log file much larger than the database, so you may need to supply sufficient disk space for the snapshot to complete.

Now if your data file is 1GB and your transaction log has grown to 40GB, I would say that there is something fishy, but if it's 10 and 20, I suspect you will just need more disk space.

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 9

The sweetwater_log.ldf 1,024KB and the sweetwater_data.mdf 25.2 GB.

C drive has available space 37.7 GB.

The replication will be failed when sweetwater_log.ldf grow until 37 ++ GB.

Thank you.

Ed

MohammedU SSCoach Points: 19644 More actions · Answer 10

Make sure your log file growth is not restricted... if it is make it unrestricted...

Run the profiler and see what is happening during that time and include error events also in your profiler..

Monitor the log file using perfmon and visual monitoring too...

Apply the lates service pack ASAP...specially your replication involved SQL 2005 also...

MohammedU
Microsoft SQL Server MVP

Jeff Gray SSChampion Points: 10667 More actions · Answer 11

Edwin,

You have approximately 1.5 times the size of the database available for the log file. There is probably nothing wrong with SQL server. You likely just need more disk space.

You might try to create the publication without the indexes, and apply them manually, one at a time. This may not be a good solution in your situation, however.

You may also try switching to the full recovery model, and truncate the log manually every few minutes with the BACKUP LOG [your database name] WITH TRUNCATE_ONLY. This might work, but I'm not so sure.

The best thing would be to get more disk space.

jeff

Jeff Gray SSChampion Points: 10667 More actions · Answer 12

Also, you can confirm what is happening at the time of the failure using the profiler. My guess is that it will be a CREATE INDEX statement.

jg

Ed7 SSCrazy Eights Points: 8516 More actions · Answer 13

After applying SP3, it works!!!!!!!!!!!

Thank youuuuuuuuuu.

MohammedU SSCoach Points: 19644 More actions · Answer 14

So, better check your serevers frequently and update them with latest SPs and patches

MohammedU
Microsoft SQL Server MVP