Back to basic question

And forgive me for asking this over and over again..
My distribution agent states " 1 transaction with 100,000 commands were
delivered "
My default profile has the Commitbatchsize as 100 and CommitBatchThreshold
as 1000.
So in this case, how does distribution agent work ? Does it read/buffer all
the 100,000 commands from msrepl_commands since its 1 transaction before it
fires them off to the subscriber ?
And how does the default profile play a role here ? If it does read/buffers
100,000 commands before it pushes it across, does it commit 1000 commands at
a time on the subscriber due to the commitbatchthreshold ? And if if fails
halfway saying after inserting 50,000 commands, does it just rollback the
last few commands that were not committed or does it rollback all 50,000
commands ?
Using transactional replication
Hassan,
why not do a test of these parameters? If you have Lumigent's logreader, you
can use it on the the live subscriber transaction log to verify the
behaviour for yourself. Create and publish a 100 record test table on the
publisher, modify the Commitbatchsize and CommitBatchThreshold parameters
and make an update of the 100 records. There's no need for an explicit
transaction, just an update of the table that affects all rows is ordinarily
logged as 100 updates with a begin and commit surrounding it. Start the
distribution agent then check Lumigent's output. The same setup can be used
for interrupted commands.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

avoiding accidental deletes...best practises?

Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

also are there any 'best practises' to avoid accidental deletes.

For example Im making a lot of delete statements like:

del from table_name
where item_id = 23

but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

Let me ask this question: would you want a script stopping half-way through a stored procedure ?

To wait for a user dialog box to complete at 4am ?

If you are having problems with accidential deletes, consider investing in Lumigent log explorer (www.lumigent.com) or Red Gates Log Rescue (www.red-gate.com) - these tools will allow you to recover the data you deleted.

Regards,

DB007

|||

use it in a transaction ....

keep autocommit off....and keep ur logs (incase it still happens) to recover the data..

|||

Hi Airwalker,

The thing to remember is accidents will happen, and just to have processes in place to help clear up the mess afterwards.

Even with transactions (if used correctly) once committed the data is still deleted (accidentially, maliciously - by a hacker !).

The options available to you are : Recover directly from the transaction log (as per previous post), Restore a prior database backup and copy the data back into the live system, make the database read-only.

Now option 3 is unlikely as this system requires updates, but suitable for systems that are designed to be read-only.

Option 2 - takes a long time, even with page level restores available in SQL 2005 - and normally a significant amount of disk space (esp. if a copy of the database needs to be taken).

Option 1 - Usually involves the least amount of time, and can be performed offline whilst users are continuing to use the system.

Best Regards,

D

|||thanks for the advice guys...much appreciated.|||

If you are running SQL Server 2005, you might consider using SQL Server 2005 database snapshots. They are great for allowing you to very easily recover data that gets altered or deleted by mistakes like that.

You could have an Agent job that periodically creates a snapshot and gets rid of older snapshots. Then, you can pull data from the most recent snapshot to fix those kind of mistakes. Just be aware that DB snapshots are by no means a replacement for a good backup strategy.

http://glennberrysqlperformance.spaces.live.com

|||

one method i have adoped is .. from my application i can delete (for Eg) only one users roles (1-M) from UserROle table.. So i have created a Trigger which checks whether deleted table contains morethan one user if yes roll back and exist.. So from QA such delete statment can be prevented....

One more method is Just create a trigger in all table which checks Count(*) from deleted table and it is 0 then it means you are deleting all the rows... roll back and raiserror..

All these methods depends... it may or may not valid in all scenario...

try this script and do the necessary modificaiton

drop table test

create table test (col1 int)

insert into test select 1

insert into test select 2

insert into test select 3

insert into test select 4

insert into test select 5

insert into test select 6

insert into test select 1

insert into test select 1

alter trigger testtrig

on test for delete

as

declare @.i int

select @.i=count(*) from test

print @.i

if @.i=0

begin

print 'u r trying to delete blah...blah'

rollback tran

end

delete from test

delete from test where col1=1

select *From test

Madhu

average time of xcusion of a request sql

hi,
i m asking about : average time of xcusion of a request sql .
thks for help
The time of execution of a SQL request is really relative:
a query is well written and the design of the DB is good when the engine
satisfies the request with the minimum number of page reads. To examine the
behavior of the engine you should execute it under Query Analizer (SQL 2000)
or SSMS (SQL 2005) activating the I/O statistics:
set statistics io on
each query executed after this command will show useful information about
I/O counters.
Looking the statistics and the query plan you can undertand if your query is
working well or not
Gilberto Zampatti
"jomu" wrote:

> hi,
> i m asking about : average time of éxécusion of a request sql .
> thks for help
>