468,133 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

What resources does truncate table use

Hi,
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would like to issue a
truncate table statement, but with millions of rows we are a bit wary
of how this will affect server performance. The alternative is to
delete is stages using rowcount but of course this will generate a
large amount of logging.
Can anyone confirm whether issuing a truncate table will have any
affect on performance - i.e. does it just clear a header block or
dooes it have to do more?
Cheers
Martin
Jul 20 '05 #1
3 4214
martin (ma*******@hotmail.com) writes:
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would like to issue a
truncate table statement, but with millions of rows we are a bit wary
of how this will affect server performance. The alternative is to
delete is stages using rowcount but of course this will generate a
large amount of logging.
Can anyone confirm whether issuing a truncate table will have any
affect on performance - i.e. does it just clear a header block or
dooes it have to do more?


TRUNCATE TABLE is indeed very efficient. All extents allocated to the
table are released, and this is all that is logged. Even for a large
table, it should not take more than a few seconds. Of course, if you
are really nervous, the only way to be sure is to restore a backup on
another server and try there first.

Note that you can not run TRUNCATE TABLE on the table if there are other
table referencing this table through foreign-key constraints.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
martin (ma*******@hotmail.com) writes:
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would like to issue a
truncate table statement, but with millions of rows we are a bit wary
of how this will affect server performance. The alternative is to
delete is stages using rowcount but of course this will generate a
large amount of logging.
Can anyone confirm whether issuing a truncate table will have any
affect on performance - i.e. does it just clear a header block or
dooes it have to do more?


TRUNCATE TABLE is indeed very efficient. All extents allocated to the
table are released, and this is all that is logged. Even for a large
table, it should not take more than a few seconds. Of course, if you
are really nervous, the only way to be sure is to restore a backup on
another server and try there first.

Note that you can not run TRUNCATE TABLE on the table if there are other
table referencing this table through foreign-key constraints.

Thanks Erland,
we are indeed very nervous but that gives me some confidence. There
are no constraints so truncate is an options
Cheers
Martin
Jul 20 '05 #3
I agree that the Truncate is the way to go. Very fast and very
efficient. One thing which I doubt you would care about is that the
Truncate will also reset any identity columns you may have. For this
purpose and the speed I use Truncate frequently.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Sumanth | last post: by
6 posts views Thread by pramod | last post: by
10 posts views Thread by Troels Arvin | last post: by
8 posts views Thread by orajit | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.