CODEWITHSUNDEEP
t-sqlsql-server-2000transaction-log
John
John

Reputation: 17471

MS-SQL 2000: Turn off logging during stored procedure

Here's my scenario:

I have a simple stored procedure that removes a specific set of rows from a table (we'll say about 30k rows), and then inserts about the same amount of rows. This generally should only take a few seconds; however, the table has a trigger on it that watches for inserts/deletes, and tries to mimic what happened to a linked table on another server.

This process in turn is unbareably slow due to the trigger, and the table is also locked during this process. So here are my two questions:

  1. I'm guessing a decent part of the slowdown is due to the transaction log. Is there a way for me to specify in my stored procedure that I do not want what's in the procedure to be logged?
  2. Is there a way for me to do my 'DELETE FROM' and 'INSERT INTO' commands without me locking the table during the entire process?

Thanks!

edit - Thanks for the answers; I figured it was the case (not being able to do either of the above), but wanted to make sure. The trigger was created a long time ago, and doesn't look very effecient, so it looks like my next step will be to go in to that and find out what's needed and how it can be improved. Thanks!

Upvotes: 2

Views: 3241

Answers (5)

DJ.
DJ.

Reputation: 16247

You can temporarily disable the trigger, run your proc, then do whatever the trigger was doing in a more efficient manner.

-- disable trigger
ALTER TABLE [Table] DISABLE TRIGGER [Trigger]
GO

-- execute your proc
EXEC spProc
GO

-- do more stuff to clean up / sync with other server
GO

-- enable trigger
ALTER TABLE [Table] ENABLE TRIGGER [Trigger]
GO

Upvotes: 1

BradC
BradC

Reputation: 39916

It doesn't help with your trigger, but the solution to the locking issue is to perform the transactions in smaller batches.

Instead of 

DELETE FROM Table WHERE <Condition>

Do something like 

WHILE EXISTS ( SELECT * FROM table WHERE <condition to delete>)
BEGIN
  SET ROWCOUNT 1000
  DELETE FROM Table WHERE <Condition>
  SET ROWCOUNT 0
END

Upvotes: 1

JoshBerke
JoshBerke

Reputation: 67068

You can't turn off transactional integrity when modifying the data. You could ignore locks when you select data using select * from table (nolock); however, you need to be very careful and ensure your application can handle doing dirty reads.

Upvotes: 1

George Mastros
George Mastros

Reputation: 24498

I wouldn't automatically assume that the performance problem is due to logging. In fact, it's likely that the trigger is written in such a way that is causing your performance problems. I encourage you to modify your original question and show the code for the trigger.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134961

1) no, also you are not doing a minimally logged operation like TRUNCATE or BULK INSERT

2) No, how would you prevent corruption otherwise?

Upvotes: 2

Related Questions