Large Truncate Performance

I have a 27 million record table that I need to truncate to free up
storage. Will this have any significat impact on what is a pretty busy 40
+GB database?Hard to tell but truncate is minimally logged and is typically extremely
fast.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?|||Truncate is a fast, nonlogged operation. It should have minimal impact =on performance. With that said, I have tried truncating a table that =large. Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
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.
-- Keith
"Brad" <me@.privacy.net> wrote in message =news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up > storage. Will this have any significat impact on what is a pretty =busy 40
> +GB database?|||Just a minor correction. Truncate is minimal logged. This is to ensure that
we would be able to rollback a transaction. Here is a quick demo.
select *
into tmp
from Northwind..Orders
go
begin tran
truncate table tmp
rollback tran
go
select count(*) as 'tmp_cnt_after_truncate' from tmp
go
drop table tmp
go
--
-oj
http://www.rac4sql.net
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OVRBue80DHA.2460@.TK2MSFTNGP10.phx.gbl...
Truncate is a fast, nonlogged operation. It should have minimal impact on
performance. With that said, I have tried truncating a table that large.
Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
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.
Keith
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?