SQL Server Performance Forum – Threads Archive

Truncate table Vs Create and Drop table

We have a DW system.
In the staging database we have to do a lot of calculations.
We use TRUNCATE statement to clean up the staging tables and populate them
My database recovery mode is ‘FULL’ My view is that using TRUNCATE will increase the transaction log size.
1. Is it advisable to replace TRUNCATE with DROP/CREATE table Option?
2. I have to give recommendation on maximum capacity my server can handle
Is there any site where we have statistics for Inserts/Update/Delete operations
for a given environment ? Advance thanks for any help Prabhu S

---

It’s really wrong thinking. Truncate is the Minimally logged operation.
Whereas Create and Drop will surely increase the Log Size.

---

Truncate is enough Run sp_spaceused before and after truncation and see if there is size difference Madhivanan Failing to plan is Planning to fail

---

Thanks for your reply. Prabhu S

---

Hi,<br />pleaser read :<br /><br /><a href=’http://www.sql-server-performance.com/q&a118.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/q&a118.asp'<a target="_blank" href=http://www.sql-server-performance.com/q&a118.asp>http://www.sql-server-performance.com/q&a118.asp</a></a><br /><br />2. I have to give recommendation on maximum capacity my server can handle<br />Is there any site where we have statistics for Inserts/Update/Delete operations<br />for a given environment ?<br /><br />It *depends* upon IO Ratio of your Disks , please refer below articles if it helps :<br /><br /><a href=’http://www.sql-server-performance.com/statistics_io_time.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/statistics_io_time.asp'<a target="_blank" href=http://www.sql-server-performance.com/statistics_io_time.asp>http://www.sql-server-performance.com/statistics_io_time.asp</a></a><br /><br /><a href=’http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf’ target=’_blank’ title=’http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf'<a target="_blank" href=http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf>http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf</a></a><br /><br /><a href=’http://www.sql-server-performance.com/jc_large_data_operations.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/jc_large_data_operations.asp'<a target="_blank" href=http://www.sql-server-performance.com/jc_large_data_operations.asp>http://www.sql-server-performance.com/jc_large_data_operations.asp</a></a><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />

---

Interesting! Ranjit, have you tested it?

---

Not Tested But from my viewpoint has to be that.
Whats your opinion????????

---

If this is really a DW, why do you run it in Full Recovery mode? Wouldn’t Simple Recovery be good enough? Do you really need the additional security of Full-Recovery like restoring to point-of-failure? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Since it is a production system, it is set as FULL RECOVERY MODE. Will there be any impact in BACKUP/RESTORE process if I change the database
to simple recovery only for the loading time and back to FULL RECOVERY
( If I am using a Differencial transaction log backup, won’t it affect ?)

---

Our DW is also in production. However, there are very few write operations. We don’t take any backup of it at all since it is completely loaded from scratch once or twice a week and if anything happens in between it is simply loaded anew. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

I don’t know how it is implemented, but IMO it might be logged as much as truncate operation. My question should be actually have you read that somewhere or tested. I would really like to know the answer. It is interesting I haven’t thought about that before. If there is significant difference between the two then it would be important to know which solution is better.

---

TRUNCATE is a minimally logged transaction. The deallocation of the pages is recorded in the log. This produces only minimal overhead. A CREATE TABLE is likely to produce similar few overhead, since the log only records the fact that a new table is being created. A DROP TABLE is, IMHO, very similar to TRUNCATE in that only the fact that a table is being dropped is recorded. So, internally I suspect SQL Server to remove some pointers and some rows from the system tables and nothing more. So, in all three cases I would suspect neglectible impact on the size of T-Log, but I would also be really interessed when someone can pass some experience here. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

I agree with Frank, as in 3 cases referred Transaction log will have its usage.
Checkhttp://www.sql-server-performance.com/q&a118.asp for reference on the truncate options. Truncate table is a logged operation–even though it is minimally logged. If it is executed within a transaction, a rollback will undo it.
Prabhu,
If you decide to keep the database in SIMPLE recovery model then ensure to maintain regular database backups that can help to recover the data in any failure conditions. Please take time to read about RECOVERY MODELS topic under books online that explains the situation and you’re at best to decide which one to opt. Can you confirm the size of database and size of transaciton log.
Also any issues on hard disk to increase the sizes? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>