deleting 11 000 000 rows...

Hi!
I just dicovered that one of our tables contains over 11 000 000 rows.
My problem right now is to delete a major part of these rows.
What I've done so far is to create a sp that handles this, but no matter how I do, the batch gets sent with all delete:s at the same time.

What I understand, the batch gets sent after the GO-statement, and at the same time, all my variables looses scope...

The data is organized on dates, one date may comprise several rows, and I know that one batch can handle all rows in one date...

The table is indexed but has no PK

The main SP:

CREATE PROCEDURE clear_lagertransaktioner
@.maxDate SMALLDATETIME
AS
DECLARE @.date SMALLDATETIME
DECLARE dateCur CURSOR FOR select distinct date from lagertransaktioner
OPEN dateCur

FETCH NEXT FROM dateCur INTO @.date
WHILE @.@.FETCH_STATUS = 0 AND @.date<@.maxDate
BEGIN

--Call another SP that I hoped would send the batch
exec del_post_lagertransaktioner @.date
FETCH NEXT FROM dateCur INTO @.date

END

CLOSE dateCur
DEALLOCATE dateCur
GO


The nested SP:
CREATE PROCEDURE del_post_lagertransaktioner
@.d SMALLDATETIME
AS

DELETE FROM lagertransaktioner WHERE date=@.d
GO

Does any of you have a better idea, cause this doesn't work.Mabye it is better for me to just insert needed data into a new table and drop the original table, since this is a once-in-a-lifetime situation (hopefully)

What do you say?

/Bix|||Two options.

Run SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) and grab your small amount of rows and insert them into a new table. Then DROP the old table and re-name the new one. Provided you have an index to select from that should provide the lowest I/O.

If you want to batch it, do something like this:

SET ROWCOUNT 10000
WHILE @.@.ROWCOUNT > 0
DELETE FROM table_name WHERE etc.

That'll delete 10,000 of them at a time, for example.

You could also re-write the DELETE to use a subquery that uses TOP 10000 to also achive this effect.

Delete of 2.5 GB

Presonally I would set up a job to
1. Backup the database
2. Delete the data
3. CLEANDB
4. DBREINDEX
5. ShrinkFile
The major inpact will be on any batch jobs happening on
that table at the same time i.e it may not be using it but
it may be linked to the process, which could cause locking.
J

>--Original Message--
>Hi,
>I have to perform a delete on a table. About 73.000
>records that actually need 2.5 GB of storage. I simply
>will perform a delete from table where .... Should i
>consider doing this after hours ? What impact should i
>consider for this action ? this is a 24x7 DB Server.
>I'm planning the delete and after do a dbcc clean table
to
>organize the table and index.
>Any suggestions or recommendations ?
>Thanks in advance
>Mike
>.
>
I agree with Julie on some points but not all. Always backup the db first.
I would do the deletes in smaller batches and you might need to backup the
tran log in between. If the rows take up 2.5GB of space then you will have
at least that much in the tran log. DBCC CLEANTABLE may not buy you
anything here since you aren't dropping any columns. I would do a DBREINDEX
but DO NOT issue a Shrinkfile unless you absolutely need that space. A
Shrinkfile will most likely undo all you just accomplished with the
DBREINDEX.
Andrew J. Kelly
SQL Server MVP
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...[vbcol=seagreen]
> Presonally I would set up a job to
> 1. Backup the database
> 2. Delete the data
> 3. CLEANDB
> 4. DBREINDEX
> 5. ShrinkFile
> The major inpact will be on any batch jobs happening on
> that table at the same time i.e it may not be using it but
> it may be linked to the process, which could cause locking.
> J
>
> to
|||Agreed, you only need to do the shrink file if want to
want to get your disk space back.
Totally agree with cleantable.
Deletion not too sure I agree, personally prefer to get
it al over at once.
Shrinkfile affecting DBREINDEX, i didn't know that
thanks, but wouldn't it just squeeze the pages together
rather than changing the order ?
J

>--Original Message--
>I agree with Julie on some points but not all. Always
backup the db first.
>I would do the deletes in smaller batches and you might
need to backup the
>tran log in between. If the rows take up 2.5GB of space
then you will have
>at least that much in the tran log. DBCC CLEANTABLE may
not buy you
>anything here since you aren't dropping any columns. I
would do a DBREINDEX
>but DO NOT issue a Shrinkfile unless you absolutely need
that space. A
>Shrinkfile will most likely undo all you just
accomplished with the
>DBREINDEX.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...
but[vbcol=seagreen]
locking.[vbcol=seagreen]
table
>
>.
>
|||Julie wrote:

> Shrinkfile affecting DBREINDEX, i didn't know that
> thanks, but wouldn't it just squeeze the pages together
> rather than changing the order ?
it changes the page order dramatically. in fact, it's sort of a goofy
situation. you shrink the db to get all the data to the front of the
file. after you shrink it, it's most likely very heavily fragmented.
so then you defrag it. when you defrag it, it moves pages out into the
free space in the db file(s), so now your pages are no longer all at the
front of the file.
|||Thanks ch,
I never knew that.
J

>--Original Message--
>Julie wrote:
>
>it changes the page order dramatically. in fact, it's
sort of a goofy
>situation. you shrink the db to get all the data to the
front of the
>file. after you shrink it, it's most likely very
heavily fragmented.
>so then you defrag it. when you defrag it, it moves
pages out into the
>free space in the db file(s), so now your pages are no
longer all at the
>front of the file.
>.
>