SEO

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

(teddysnips@hotmail.com) writes:
> 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.

But the script in a file and the run the job as a command-line job that
fires up the script with ISQL.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns961423BE5D98Yazorman@127.0.0.1>...
> (teddysnips@hotmail.com) writes:
> > 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.
>
> But the script in a file and the run the job as a command-line job that
> fires up the script with ISQL.

Could I please check that I have this correct?

I put the script into a file - let's say, "myquery.sql"

The Type of the task changes from TSQL to CmdExec.

In the command window I enter:

isql.exe /i myquery.sql

Is that it? If so, where should myquery.sql be located, for isql.exe
to find it? Or do I need to supply an absolute path? And should the
path be in quotes?

Normally I would research this myself, but as I stated in my original
post this contract only has a few weeks to run, and the new contract
is on SQL 2k so this won't be an issue.

Many thanks again.

Edward

Edward (teddysnips@hotmail.com) writes:
> Could I please check that I have this correct?
>
> I put the script into a file - let's say, "myquery.sql"
>
> The Type of the task changes from TSQL to CmdExec.
>
> In the command window I enter:
>
> isql.exe /i myquery.sql
>
> Is that it? If so, where should myquery.sql be located, for isql.exe
> to find it? Or do I need to supply an absolute path? And should the
> path be in quotes?

As with any other command-line utility, ISQL will read from the current
directory. This is, if memory serves, %WINNT%/SYSTEM32. It goes without
saying that this is not a good place to put query script. So an absolute
path is to recommend. Quotes you need if the path contains special
characters.

It's also good to specify /o to get an output file. And /n to be saved from
all the 1> 2> etc.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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
>