Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [truncate]

A DDL operation that quickly removes all rows.

A DDL operation that quickly removes all rows.

In Oracle and MySQL(depending on the storage engine) the operation is not transactional and cannot be rolled back.

In PostgreSQL and SQL Server the statement must be committed or rolled back.

In SQL Server, Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

References:

TRUNCATE TABLE (Transact-SQL)

108 questions
105
votes
7 answers

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost all of whom are much more experienced than I am)…
user606723
  • 1,496
  • 4
  • 14
  • 16
25
votes
4 answers

Truncated 200GB table but disk space not released

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
Lucas Rodrigues Sena
  • 377
  • 1
  • 4
  • 7
21
votes
1 answer

PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1

Is there any way to reset all the sequences of tables, when truncate a table on cascade. I already read this post How to reset sequence in postgres and fill id column with new data? ALTER SEQUENCE seq RESTART WITH 1; UPDATE t SET…
YCF_L
  • 313
  • 1
  • 2
  • 8
20
votes
5 answers

What permissions are necessary for truncating a table?

I have a SQL account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION [dbo] GO GRANT EXECUTE TO [db_executor] GO When I…
Mansfield
  • 931
  • 8
  • 15
  • 32
20
votes
2 answers

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the DELETE/TRUNCATE commands. The results that I am getting are…
孔夫子
  • 4,258
  • 3
  • 26
  • 49
20
votes
2 answers

Truncate a table with 17 billion rows in an AG

I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG. What will be the effect of this operation on the AG latency and the size of log backups? Is there a recommended way of doing this?
yegnasew
  • 455
  • 4
  • 7
17
votes
1 answer

Safe way to truncate SQL Server Error Log

We are running out of space. What is the safe way to clear the error log?
aron
  • 555
  • 2
  • 4
  • 8
16
votes
3 answers

Why is truncate DDL?

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ? Q. Why Truncate is DDL Where as Delete is DML ?…
Ravi
  • 1,455
  • 3
  • 15
  • 20
15
votes
1 answer

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The disk space was released after ~12 hours, without…
Adam Matan
  • 10,129
  • 27
  • 75
  • 94
13
votes
1 answer

Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is released. I've read that the table is truncated at the…
Mark Freeman
  • 1,938
  • 5
  • 24
  • 43
11
votes
2 answers

Postgres suggests "Truncate table ... at the same time"

When running: TRUNCATE TABLE YYYYY RESTART IDENTITY I am seeing this error message: ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "XXXXX" references "YYYYY". HINT: Truncate table "XXXXX" at the same time, or…
davetapley
  • 753
  • 3
  • 7
  • 14
10
votes
1 answer

TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE. Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = False. Procedure1 (Replaces content of table foo…
Mark Freeman
  • 1,938
  • 5
  • 24
  • 43
10
votes
7 answers

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. The hanging statement is simply: TRUNCATE TABLE…
Haseena
  • 451
  • 3
  • 6
  • 15
9
votes
1 answer

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands records. For some reason, the TRUNCATE TABLE command…
Ran
  • 1,443
  • 7
  • 20
  • 34
8
votes
4 answers

Difference between DROP and TRUNCATE

What does the TRUNCATE do differently to drop? I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?
user1829823
  • 189
  • 1
  • 1
  • 2
1
2 3 4 5 6 7 8