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.
>|||Tony's solution may do it for you. Another alternative is to use a loop,
deleting a manageable chunk each time and backing up the log immediately
afterwards.
--
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:%23LBvLHPvEHA.3972@.TK2MSFTNGP10.phx.gbl...
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:
> > 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)
> >
> >
> >
>|||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)
>