Re: urgent!!!
From: DBAdmin (DBAdmin_at_discussions.microsoft.com)
Date: 07/13/04
- Next message: Gary: "Browse date"
- Previous message: Geoff N. Hiten: "Re: Windows 2000 or 2003"
- Maybe in reply to: Marcos MOS: "urgent!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 12 Jul 2004 19:18:02 -0700
One of us has an incomplete and/or inaccurate understanding of SQL Server in this context. It could be me, but maybe somebody with a deeper understanding will comment.
As I understand it, there is no data modification that is not logged. My use of BEGIN TRANSACTION did not cause logging to occur, it simply provided a wrapper around the TRUNCATE TABLE that allowed an explicit ROLLBACK. The wording in BOL is misleading on this issue. If the recovery model is set to Simple, then non-logged is somewhat, but not completely, correct. In that case, the transaction log is cleared after each transaction is completed. If the recovery model is set to Bulk-Logged or Full, the page deallocations of TRUNCATE TABLE remain in the transaction log until it's backed up. The transaction log is not cleared until that point. There are at least three third-party tools that can read the transaction log and access the data.
I would be grateful if somebody else would confirm or correct my understanding of this behavior.
"Joe" wrote:
> Well aren't we Mr. High and Mighty.
>
> Man, give a guy a break will ya.
>
> Yes I did say it was a 'non-logged' transaction. In your 1st post, you used
> a BEGIN TRANSACTION & END TRANSACTION statement. Which is a logged
> transaction. That answers your question.
>
> But to make sure you understand where I was coming from, here is the BOL
> entry for TRUNCATE TABLE. (Note the 1st sentenced):
>
> Deleting All Rows Using TRUNCATE TABLE
> The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all
> rows in a table. It is almost always faster than a DELETE statement with no
> conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs
> only the deallocation of whole data pages. TRUNCATE TABLE immediately frees
> all the space occupied by that table's data and indexes. The distribution
> pages for all indexes are also freed.
>
> As with DELETE, the definition of a table emptied using TRUNCATE TABLE
> remains in the database, along with its indexes and other associated
> objects. The DROP TABLE statement must be used to drop the definition of the
> table.
>
> To delete all rows in a table using TRUNCATE TABLE
>
> Transact-SQL
>
>
> See Also
>
> DROP TABLE
>
> ©1988-2004 Microsoft Corporation. All Rights Reserved.
>
>
> "DBAdmin" <DBAdmin@discussions.microsoft.com> wrote in message
> news:02AC96B7-1B1F-4297-88B7-2EE7A0A8AF73@microsoft.com...
> > I understand the distinction. Does Joe? His message suggests that the
> operation can't be rolled back. Clearly it can. His message suggests that
> the data in question is definitely gone. I'm not sure that's true.
> >
> > I think that if the database in question is not using the Simple recovery
> model then the page deallocations are still present in the transaction log.
> With an appropriate (but not cheap) tool, the data might be recoverable.
> >
> > If Joe understood the difference between not logged (not even possible)
> and minimally logged, I would think this would have been THE opportunity to
> explain it to a SQL Server user in distress. Instead, his message left no
> indication of any hope.
> >
> > "Dan Guzman" wrote:
> >
> > > TRUNCATE is minimally logged. The terms non-logged and minimally-logged
> are
> > > often used interchangeably but minimally-logged is technically more
> correct.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "DBAdmin" <DBAdmin@discussions.microsoft.com> wrote in message
> > > news:D7A9945E-2740-4E8A-B418-3CE3737FC17D@microsoft.com...
> > > > If TRUNCATE TABLE is not logged, how would you explain the behavior of
> > > this sample code?
> > > >
> > > > CREATE TABLE MyTest (MyTestID int, MyTest varchar(80))
> > > >
> > > > INSERT MyTest VALUES (1,'W')
> > > > INSERT MyTest VALUES (2,'X')
> > > > INSERT MyTest VALUES (3,'Y')
> > > > INSERT MyTest VALUES (4,'Z')
> > > >
> > > > BEGIN TRANSACTION
> > > >
> > > > SELECT * FROM MyTest
> > > >
> > > > TRUNCATE TABLE MyTest
> > > >
> > > > SELECT * FROM MyTest
> > > >
> > > > ROLLBACK
> > > >
> > > > SELECT * FROM MyTest
> > > >
> > > > DROP TABLE MyTest
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > If you have no backups then there is nothing more to be done.
> TRUNCATE
> > > table
> > > > > is a non-logged transaction so there is no help there.
> > > > >
> > > > > I'm afraid the data is lost. :(
> > > > >
> > > > > Joe
> > > > >
> > > > >
> > > > > "Marcos MOS" <marcos.santos@softway.com.br> wrote in message
> > > > > news:eBuSSEfZEHA.2500@TK2MSFTNGP09.phx.gbl...
> > > > > > Thanks, but my question was without backup...
> > > > > > any suggestions?
> > > > > >
> > > > > > "Joe" <JoeD777@lycos.com> escreveu na mensagem
> > > > > > news:#urPnVeZEHA.2972@TK2MSFTNGP12.phx.gbl...
> > > > > > > you got a backup? if so, restore to a temp db and then migrate
> the
> > > table
> > > > > > > over.
> > > > > > >
> > > > > > > hth
> > > > > > >
> > > > > > >
> > > > > > > "Marcos MOS" <marcos.santos@softway.com.br> wrote in message
> > > > > > > news:OheR4DeZEHA.2016@TK2MSFTNGP09.phx.gbl...
> > > > > > > > Hi!
> > > > > > > >
> > > > > > > > a user has just executed a TRUNCATE statement against a sql
> > > server's
> > > > > > > > table...
> > > > > > > > But it was a his mistake!!! :)
> > > > > > > >
> > > > > > > > How can I restore the data (rows) fastest?
> > > > > > > >
> > > > > > > > thanks a lot!
> > > > > > > > Marcos
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
- Next message: Gary: "Browse date"
- Previous message: Geoff N. Hiten: "Re: Windows 2000 or 2003"
- Maybe in reply to: Marcos MOS: "urgent!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
