About the use of TRUNCATE TABLE command

Hi all,
I have a big table with 54 Million of records. I want to use the TRUNCATE TABLE command to eliminate all the records. I don't want to drop the table, just delete records.

But I have questions about the use of this command:

1) How much time does this command execution could it take?
2) I want to this (I need to this) in production enviroment. Will you do it?

Thanks in advance!

Hi,

1) This depends on your machine, Truncate table just deallocates the data pages and uses a minimum of log space, it should be quiet fast for your 54m rows.
2) What do you mean, if we WOULD (or will ? You probably will have to do it :-)) do it ? You will have to keep in mind that there is NO way back after truncating, the only way would be to restore the data from a backup.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi and thanks Jens,

1) Ok, the machine is in production environment. So, that's my worry. I'm asking this to have an idea about the time this command spends in an production environment. I don't want to get the server slow for this command.
2) And yes, I know that this action delete rows and don't log to much: that's what I need.

Thanks again.

about MSRepl_commands Table

Hi,
We're running sql2000 with transactional replication. I found that the
system table distribution.msrepl_commands has over 3 million records. So, I
execute the command "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0" manually. As the result, the no. of records still
remain 2.5 million records. Why? I checked with distribution clean jobs was
success everytime. How can I minimize the table size?
Thx!
Ron
are you using anonymous subscriptions? If so this might explain what you are
seeing? Also are your distribution agents running frequently? Are any of
them stopped. The clean up normally happens when they run.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We're running sql2000 with transactional replication. I found that the
> system table distribution.msrepl_commands has over 3 million records. So,
> I execute the command "EXEC dbo.sp_MSdistribution_cleanup
> @.min_distretention = 0, @.max_distretention = 0" manually. As the result,
> the no. of records still remain 2.5 million records. Why? I checked with
> distribution clean jobs was success everytime. How can I minimize the
> table size?
> Thx!
> Ron
>
|||Yes! I'm using anonymous subscriptions and clean up every 3 hours. The clean
up jobs seems to work success according to the status of SQLServer Agent.
Any things I can do to minimize the size?
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...
> are you using anonymous subscriptions? If so this might explain what you
> are seeing? Also are your distribution agents running frequently? Are any
> of them stopped. The clean up normally happens when they run.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "555 Rider" <ronchukw@.yahoo.com> wrote in message
> news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||query distribution.dbo.MSdistribution_status
Are most the command delivered or waiting to be delivered? If they are
waiting to be delivered you should look at improving the performance of your
distribution agents, like using pull subscriptions and changing the
PollingInterval to 1,
If the commands are mostly delivered, your problem truly is with them being
cleaned up; try to have the distribution clean up agent run every 10
minutes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:ex7unWchFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Yes! I'm using anonymous subscriptions and clean up every 3 hours. The
clean
> up jobs seems to work success according to the status of SQLServer Agent.
> Any things I can do to minimize the size?
> Ron
>
> "Hilary Cotter" <hilary.cotter@.gmail.com>
glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...[vbcol=seagreen]
any[vbcol=seagreen]
So,[vbcol=seagreen]
result,[vbcol=seagreen]
with
>