Avoid increasing the log file

Hello All,

I have faced a network problem during some days, what forced one of our replications to be stopped.
The Publisher database is a high volume database.
After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.

My concern is the way it is working, there will be no enough space for the log or for its backup files.

So, I have created a TSQL job within the following commands:

BACKUP LOG database_name
WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)

It's running every 20 minutes, however the transaction log remains increasing.

I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.

Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?

Thanks a lot!
Regards,
Felicia Schimidt
felicia.schimidt@.br.flextronics.comDon't really know anything about internals on MS replication...

On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"|||Hi,

Thanks for your help, but I could not find anything like gettrunc()...

Please, does anybody know anything about the issue below?
I am really in trouble!

Thanks again!
Felicia

Originally posted by fadace
Don't really know anything about internals on MS replication...

On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"|||If you're truncating the log with TRUNCATEONLY you are effectively destroying it, so you might as well ALTER DATABASE the database to SIMPLE RECOVERY mode, and you won't get a transaction log at all.

In essence, your real problem happens because a significant portion of the log is 'active' - in other words holding an open transaction, at the point the you are issuing the truncate command. Do you have a large long running transaction hanging fire?

Generally, you'll be better off either putting very large storage behind a real transaction log and backing off the log properly, or doing away with it altogether if your recovery policy will permit. Growing and shrinking a transaction log repetitively will lead to considerable disk fragmentation over time and cause performance problems particularly as you obviously have a very large load.

also

"For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

"If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_1l4e.asp|||Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

You may also want to turn off "auto shrink" to avoid performance issue.|||Hi,

Thanks for you help.

I have setup the database to simple recovery mode and turnning subscriber agent on.
Unfortunnatelly the Transaction log grew again quickly and no disk space was availble.

Is there any other way to force the database to not use the transaction log?

Thanks a lot,
Felicia

Originally posted by rding
Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

You may also want to turn off "auto shrink" to avoid performance issue.|||some non-logged operations don't seem to fit in your scenario. You probably need to reset up the replication.