microsoft.public.sqlserver.server

Author

Full Recovery Mode

Mark

2002-12-20, 9:24 am

Our database is in Full Recovery mode. I was surprised to
see that the Truncate Table command can be executed when
the database is in this mode. Are all transaction log
backups essentially shot for the rest of the day after
this command is executed?

Below is from BOL, but I couldn't find anything regarding
Truncate Table. Thanks!

-Mark

When FULL is specified, database backups and transaction
log backups are used to provide full recoverability from
media failure. All operations, including bulk operations
such as SELECT INTO, CREATE INDEX, and bulk loading data,
are fully logged. For more information, see Full Recovery.

Dan Guzman

2002-12-20, 10:23 am

Using TRUNCATE TABLE is not a recovery model consideration. The
statement is fully recoverable because the page de-allocations are still
recorded in the transaction log. Consequently, it behaves just like any
other T-SQL statement as far as recovery is concerned.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Mark" <field027@umn.edu> wrote in message
news:033a01c2a837$6c7e9d60$8ef
82ecf@TK2MSFTNGXA04...
> Our database is in Full Recovery mode. I was surprised to
> see that the Truncate Table command can be executed when
> the database is in this mode. Are all transaction log
> backups essentially shot for the rest of the day after
> this command is executed?
>
> Below is from BOL, but I couldn't find anything regarding
> Truncate Table. Thanks!
>
> -Mark
>
> When FULL is specified, database backups and transaction
> log backups are used to provide full recoverability from
> media failure. All operations, including bulk operations
> such as SELECT INTO, CREATE INDEX, and bulk loading data,
> are fully logged. For more information, see Full Recovery.

Yih-Yoon Lee [MS]

2002-12-20, 10:24 am

Hi,

I believe you can find the answer here as well

INF: Effects of Nonlogged and Minimally Logged Operations on Transaction
WGID:249
ID: 272093.KB.EN-US
http://support.microsoft.com/suppor...s/q272/0/93.asp

Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Allan Mitchell

2002-12-20, 10:24 am

TRUNCATE is also included in that statement

Try it

CREATE DATABASE FullTest

backup database FullTest to disk = 'c:\db.bak'

Use Fulltest

CREATE TABLE Allan (col1 int)

insert Allan Values(1)

truncate table Allan

--Backup the log
backup log FullTest to disk = 'c:\log.log'

--restore the db

Use master

restore database Fulltest from disk = 'c:\db.bak' with norecovery

--and the log.
restore log fulltest from disk = 'c:\log.log' with recovery

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mark" <field027@umn.edu> wrote in message
news:033a01c2a837$6c7e9d60$8ef
82ecf@TK2MSFTNGXA04...
> Our database is in Full Recovery mode. I was surprised to
> see that the Truncate Table command can be executed when
> the database is in this mode. Are all transaction log
> backups essentially shot for the rest of the day after
> this command is executed?
>
> Below is from BOL, but I couldn't find anything regarding
> Truncate Table. Thanks!
>
> -Mark
>
> When FULL is specified, database backups and transaction
> log backups are used to provide full recoverability from
> media failure. All operations, including bulk operations
> such as SELECT INTO, CREATE INDEX, and bulk loading data,
> are fully logged. For more information, see Full Recovery.

Sponsored Links