T-SQL too big for Task Scedule - HELP! 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 |