Do not write to transaction log

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Do not write to transaction log

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-09-27 : 16:52:41

Is it possible to run query, but not have that query record in the transaction log? I have to delete several thousand records and I do not want them to fill up my log.

Ryan

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 16:55:04

No, that is not possible. What you can do is a controlled delete though. Check this out:

http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 16:57:27

Or here is a better one that I wrote recently:



SET NOCOUNT ON

DECLARE @RowCnt INT

SELECT TOP 100000 ID AS TrailerID
INTO #Temp
FROM Trailer
WHERE DP_LOAD_ID NOT IN (SELECT ID FROM DP_LOAD)

SET @RowCnt = @@ROWCOUNT

SET ROWCOUNT 5000

WHILE @RowCnt > 0
BEGIN

	DELETE t1
	FROM Trailer t1
	INNER JOIN #Temp t2
	ON t1.ID = t2.TrailerID

	SET @RowCnt = @@ROWCOUNT

	WAITFOR DELAY '00:00:30'

END

SET ROWCOUNT 0

DROP TABLE #Temp

I scheduled the above to run every night until the rows were deleted. 100000 per night in 5000 batches.

Tara

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-09-27 : 16:58:53

Thanks Tara, as always you are on the ball!

Ryan

Ryan Everhart
SBC

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-27 : 17:00:12

How many do you want to keep?

SELECT * INTO myNew Table FROM myTable WHERE what I want to keep

DROP TABLE myTable

Rename NewTable to OldTable

Minimally logged there, and quick...

But if like Tara said you've got HUGE amounts of data...

(thousand doesn't qualify by the way)

My best guess is that you really need to set up a tranny dump.

Do you have any maintenance setup at all?

Brett

8-)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 17:37:25

That's good Brett Why not Truncate Source and Append from Temp

We are assuming their are no foreign key constraints which would also have to be dropped and recreated... I would'nt do this unless I was in single user mode.

Surf On Dude!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources