Commit, rollback...

23 Jan 2006 1:17 PM

TLehtinen

Hello!

I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...

How do I disable rollback feature one time on my deletequery?

Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.

And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.

T

23 Jan 2006 1:34 PM

Rick Sawtell

Show quote Hide quote

"TLehtinen" <t***@raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@news.song.fi...
> Hello!
>
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
>
> How do I disable rollback feature one time on my deletequery?
>
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
>
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
>
> T
>

Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:

DECLARE @rowid    int
SET @rowid = 10000
WHILE @rowid < 28449928
BEGIN
    BEGIN TRAN
        DELETE    tablename
        WHERE    id <= @rowid

        BACKUP LOG <databasename> WITH TRUNCATE_ONLY

        SET @rowid = @rowid + 10000
END -- Loop

Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.

Rick Sawtell
MCT, MCSD, MCDBA

23 Jan 2006 2:46 PM

Rick Sawtell

Ooops.

I forgot to stick a COMMIT in there.

DECLARE @rowid    int
SET @rowid = 10000
WHILE @rowid < 28449928
BEGIN
    BEGIN TRAN
        DELETE    tablename
        WHERE    id <= @rowid

        IF @@Error <> 0
            ROLLBACK
        ELSE
            COMMIT

        BACKUP LOG <databasename> WITH TRUNCATE_ONLY

        SET @rowid = @rowid + 10000
END -- Loop

24 Jan 2006 10:35 AM

TLehtinen

I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)

Cannot perform a backup or restore operation within a transaction.

And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour....

"Rick Sawtell" <Quicken***@msn.com> kirjoitti

viestissä:O0cr8uCIGHA.3***@TK2MSFTNGP12.phx.gbl...
Show quoteHide quote

> Ooops.
>
> I forgot to stick a COMMIT in there.
>
> DECLARE @rowid    int
> SET @rowid = 10000
> WHILE @rowid < 28449928
> BEGIN
>    BEGIN TRAN
>        DELETE    tablename
>        WHERE    id <= @rowid
>
>        IF @@Error <> 0
>            ROLLBACK
>        ELSE
>            COMMIT
>
>        BACKUP LOG <databasename> WITH TRUNCATE_ONLY
>
>        SET @rowid = @rowid + 10000
> END -- Loop
>
>

24 Jan 2006 12:30 PM

Steve L

See posting 4 - the script was updated as Rick missed out a commit

Cheers

Steve L

24 Jan 2006 1:23 PM

TLehtinen

Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @rowid    int
SET @rowid = 17000000
WHILE @rowid < 28000000
BEGIN
    BEGIN TRAN
        DELETE    dbo.stat
        WHERE    id <= @rowid

        IF @@Error <> 0
            ROLLBACK
        ELSE
            COMMIT

        BACKUP LOG stat WITH TRUNCATE_ONLY

        SET @rowid = @rowid + 10000
END -- Loop

And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.

"Steve L" <steve_lawren***@tiscali.co.uk> kirjoitti

viestissä:1138105823.735961.52***@g44g2000cwa.googlegroups.com...
Show quoteHide quote

> See posting 4 - the script was updated as Rick missed out a commit
>
> Cheers
>
> Steve L
>

24 Jan 2006 2:15 PM

Steve L

Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @rowid    int
SET @rowid = 17000000
WHILE @rowid < 28000000
BEGIN
          WHILE @ROWCOUNT < 10001
        BEGIN
                   DELETE    dbo.stat
                   WHERE    id = @rowid
                    SELECT @rowid = @rowid+1, @rowcount=@rowcount+1
        END
        Select @rowcount = 0

       BACKUP LOG stat WITH TRUNCATE_ONLY

        --SET @rowid = @rowid + 10000
END -- Loop

Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

23 Jan 2006 1:41 PM

MC

To minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.

MC

Show quoteHide quote

"TLehtinen" <t***@raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@news.song.fi...
> Hello!
>
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
>
> How do I disable rollback feature one time on my deletequery?
>
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
>
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
>
> T
>