Commit, rollback...

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
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
"TLehtinen" <timo@.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
>
|||"TLehtinen" <timo@.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
|||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
|||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" <Quickening@.msn.com> kirjoitti
viestiss:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> 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
>
|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
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
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_lawrenson@.tiscali.co.uk> kirjoitti
viestiss:1138105823.735961.52530@.g44g2000cwa.goog legroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> 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