logo
Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Options
Go to last post Go to first unread
bart  
#1 Posted : Tuesday, November 13, 2012 8:43:14 AM(UTC)
bart

Rank: Administration

Reputation:

Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188
Man

Was thanked: 1 time(s) in 1 post(s)
If you want to delete millions of records with an sql query you run into the folloing problem.
The sql log will become really large and the disk gets full.
With a query like this you can prevent this. I wrote this query backward compatible with sql 2000. If you use 2008 or later you can write a shorter one.

Code:

  Declare @deleted int 
  set @deleted = 1 
  while @deleted >0  
    begin 
        delete from [tablename]
        where id in(
        select top 100000 id from [tablename]
        where error = '1')
      set @deleted = @@ROWCOUNT 
	  DBCC SHRINKFILE(2, 2) 
	  DBCC SHRINKFILE(1, 2,TRUNCATEONLY) 
   end 

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.