Sounds like the ideal thing is to put the database in Simple mode so it does
not write a log. However if SQL Server thinks the database is still marked
for replication it will not allow you to do this.

Sometimes SQL is not too clever at removing the information marking a db for
replication, I have had trouble with this in the past, but I eventually have
succeeded in removing it (unfortunately I cannot remember the exact steps).
A search on google should provide a few clues.

You could try copying the db to a server that is not enabled for replication
(either file copy or backup and restore), which should give you an unmarked
database that you can copy back.



<teddysnips@hotmail.com> wrote in message
news:1110296774.382661.56930@o13g2000cwo.googlegro ups.com...
> SQL Server 6.5
>
> My clients are troubled by a growing Transaction log, but they don't
> want a lot of expensive maintenance as the contract is about to expire.
> The database used to be marked for replication, but it seems almost
> impossible to stop records being written to the Transaction log even
> though there are no Publishers and no Subscribers. Thus the
> transaction log fills up with publication transactions for which there
> is no subscriber. Various on-line sources suggest marking all such
> records as "complete", allowing the log to be truncated (this works -
> I've tested it in the query window)
>
> I suggested a Scheduled Task to truncate the log, and I want to put
> this script into a task:
>
> use master
> go
> sp_configure 'allow', 1
> go
> reconfigure with override
> go
> begin tran
> update master..sysdatabases set category = 1 where name = 'WidgetData'
> commit tran
> use WidgetData
> go
> sp_repldone 0, 0, NULL, 0, 0, 1
> dump tran WidgetData with truncate_only
> update sysobjects set category = category & ~32
> update sysobjects set category = category & ~64
> use master
> go
> update sysdatabases set category = 0 where name = 'WidgetData'
> go
> sp_configure 'allow', 0
> go
> reconfigure with override
> go
>
>
> However, the text box where I can enter the SQL truncates at 231
> characters.
>
> What can I do, short of running the job myself? Can I split it into
> two or more tasks? If so, where should I split it? And can I
> establish a dependency between them so that, in the event of one task
> failing, the other(s) don't execute.
>
> Your help would be greatly appreciated.
>
> Edward
>