Re: question regarding truncate operation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Correct. Nowadays, we actually do use the terms fully and minimally logged. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Vadivel" <Vadivel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3AF77A34-8CDC-4DAF-A0D0-BCEA6718AAC6@xxxxxxxxxxxxxxxx
We can say that, the deallocation of data pages means that the data rows
still actually exist in the data pages. But the Extents have been marked as
empty for reuse.

Also I don't think there is anything in SQL Server which is not logged. How
much of it gets logged would be the difference between various commands.

Tony/Tibor: Correct me if I am wrong with this stmt :)

Best Regards
Vadivel

http://vadivel.blogspot.com


"Tony Rogerson" wrote:

truncate table - if that is what you are talking about Pradeep is still
transaction aware...

select *
into fred
from sys.objects
go

begin tran

truncate table fred

select COUNT(*) from fred

rollback

select COUNT(*) from fred


The table "fred" will still be populated

If you create a delete trigger on "fred" then that will not fire.

Once you have committed its like you have "saved" the word document - its
done, and dusted - the only way to recover is to restore the database and
log to a point in time before the transaction you did.

Tony.

"SqlBeginner" <SqlBeginner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6301E139-1169-4463-BFA2-CAB0389A3B43@xxxxxxxxxxxxxxxx
>I read that truncate logs the deallocation of the data pages in >which the
> data exists.
>
> Actually wanted to know more on what exactly is getting logged?
>
> When truncate is called within a transaction and if that > transaction is
> rolled back the data is recovered smoothly. When sql server can > do it why
> it
> cannot do for committed transactions?
>
> Regards
> Pradeep


.

Relevant Pages

  • Re: urgent!!!
    ... One of us has an incomplete and/or inaccurate understanding of SQL Server in this context. ... > a BEGIN TRANSACTION & END TRANSACTION statement. ... > Deleting All Rows Using TRUNCATE TABLE ...
    (microsoft.public.sqlserver.server)
  • Re: another logging question
    ... Your load will fail if SQL Server cannot write to the log because its full. ... You will get a transaction log full error and the transaction will get ... > it looks like using the simple recovery plan would work best for us. ... I'm assuming it will truncate when the ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log truncation on full backup
    ... As Uri posted, full backup does not truncate the log, this is easy to test. ... SQL Server is smart enough to not replay the same transaction twice... ...
    (microsoft.public.sqlserver.server)
  • Re: question regarding truncate operation
    ... log to a point in time before the transaction you did. ... >I read that truncate logs the deallocation of the data pages in which>the ... > cannot do for committed transactions? ...
    (microsoft.public.sqlserver.programming)
  • Re: question regarding truncate operation
    ... Also I don't think there is anything in SQL Server which is not logged. ... log to a point in time before the transaction you did. ... When truncate is called within a transaction and if that transaction is ... cannot do for committed transactions? ...
    (microsoft.public.sqlserver.programming)