DENY permission,SQL server: Delete without log ?

Hi all,
I want to make some DELETE on a lot of data without writting anything in the
DB log file ?
How to do that ?
Lilian.
Depends on what you mean by "a lot". If you mean "all", then you can use
TRUNCATE TABLE. Otherwise, you'll have to use a DELETE, which is always
logged.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:ubzQ%234OvEHA.1524@.TK2MSFTNGP09.phx.gbl...
Hi all,
I want to make some DELETE on a lot of data without writting anything in the
DB log file ?
How to do that ?
Lilian.
|||Truncate table will give minimal logging, but that removes all data in the
table.
If you are keeping only a small subset of the data, might be more log
efficient to use SELECT INTO to create a new table and drop the old one,
then rename the new table back to the old.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||Oh

(
I can't use Truncate... then another (dirty) solution: how to shrink the log
file after the DELETE ?
DBCC SHRINKDATABASE doesn't seem to work...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OgTJJCPvEHA.3080@.TK2MSFTNGP12.phx.gbl...
> Depends on what you mean by "a lot". If you mean "all", then you can use
> TRUNCATE TABLE. Otherwise, you'll have to use a DELETE, which is always
> logged.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:ubzQ%234OvEHA.1524@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I want to make some DELETE on a lot of data without writting anything in
the
> DB log file ?
> How to do that ?
> Lilian.
>
|||You could alway change the recovery model to "simple" and put it back to
"Full Recovery" after your delete statement(s) if you absolutely need nothing
to be logged. I would be unconfortable to do that on a prod. DB.
Sasan Saidi
"Tony Rogerson" wrote:

> Truncate table will give minimal logging, but that removes all data in the
> table.
> If you are keeping only a small subset of the data, might be more log
> efficient to use SELECT INTO to create a new table and drop the old one,
> then rename the new table back to the old.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>
|||Changing the recovery model to simple does not stop the logging of DELETE's.
This, if she has one monster DELETE, the log could still fill up.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8FA6ACC9-6D5C-4A8C-8B2C-A6B4A9608707@.microsoft.com...
You could alway change the recovery model to "simple" and put it back to
"Full Recovery" after your delete statement(s) if you absolutely need
nothing
to be logged. I would be unconfortable to do that on a prod. DB.
Sasan Saidi
"Tony Rogerson" wrote:

> Truncate table will give minimal logging, but that removes all data in the
> table.
> If you are keeping only a small subset of the data, might be more log
> efficient to use SELECT INTO to create a new table and drop the old one,
> then rename the new table back to the old.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>
|||It will still get logged, but eventually get recycled right? which is as good
as not logging... which may serve Lilian's purpose...
Thanks
GYK
"Tom Moreau" wrote:

> Changing the recovery model to simple does not stop the logging of DELETE's.
> This, if she has one monster DELETE, the log could still fill up.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:8FA6ACC9-6D5C-4A8C-8B2C-A6B4A9608707@.microsoft.com...
> You could alway change the recovery model to "simple" and put it back to
> "Full Recovery" after your delete statement(s) if you absolutely need
> nothing
> to be logged. I would be unconfortable to do that on a prod. DB.
> Sasan Saidi
> "Tony Rogerson" wrote:
>
>
|||If the table is 1GB and you do a delete for all the rows then you'll get
slightly more than 1GB on the log, this can't be truncated (recycled) until
the transaction has completed.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||It's exactly my case... and after a lot of test I use the TRUNCATE (with
temp tables to keep the data that I need)
1) create table xxx_tmp
2) insert into xxx_tmp from xxx where yyy = zzz and ...
3) TRUNCATE TABLE xxx
4) insert into xxx from xxx_tmp
5) drop table xxx_tmp
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:OCFfJOSvEHA.4020@.TK2MSFTNGP10.phx.gbl...
> If the table is 1GB and you do a delete for all the rows then you'll get
> slightly more than 1GB on the log, this can't be truncated (recycled)
until
> the transaction has completed.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>

Wednesday, March 7, 2012

Delete without log ?

No comments:

Post a Comment

DENY permission,SQL server

Blog Archive

About Me