Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [truncate]

An operation that removes all rows in a table without causing any triggered action

Filter by
Sorted by
Tagged with
107 votes
7 answers
45k views

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 ...
user606723's user avatar
  • 1,526
44 votes
1 answer
84k views

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 ...
Youcef LAIDANI's user avatar
26 votes
4 answers
84k views

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's user avatar
24 votes
5 answers
105k views

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 [...
Mansfield's user avatar
  • 1,031
21 votes
2 answers
5k views

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 ...
yegnasew's user avatar
  • 487
21 votes
2 answers
22k views

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 &...
davetapley's user avatar
20 votes
2 answers
4k views

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/...
孔夫子's user avatar
  • 4,330
18 votes
1 answer
44k views

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's user avatar
  • 595
16 votes
3 answers
32k views

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....
Ravi's user avatar
  • 1,555
16 votes
1 answer
8k views

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 ...
Adam Matan's user avatar
  • 11.7k
13 votes
1 answer
9k views

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 ...
Mark Freeman's user avatar
  • 2,181
10 votes
7 answers
36k views

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. ...
Haseena's user avatar
  • 571
10 votes
1 answer
4k views

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 = ...
Mark Freeman's user avatar
  • 2,181
9 votes
4 answers
52k views

Truncate with where clause

Can I use truncate command with a where clause? I need to remove specific rows from several tables. How can I delete specific data from the entire database? SELECT DimEmployee.[FirstName], ...
l.lijith's user avatar
  • 898
9 votes
2 answers
33k views

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 ...
Ran's user avatar
  • 1,553
8 votes
4 answers
28k views

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's user avatar
7 votes
2 answers
12k views

How to get last truncated time for each tables in SQL Server database

I have a SQL Server 2012 database. Is it possible to get last truncated time for all tables in the database? Note: Recovery mode is SIMPLE in my scenario. Is it possible if I change it to FULL ?
LCJ's user avatar
  • 900
7 votes
1 answer
3k views

Truncate parent table but not children

I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of ...
sheepdog's user avatar
  • 345
7 votes
2 answers
10k views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
Clarkey's user avatar
  • 425
7 votes
2 answers
3k views

Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

Maybe I am missing something here: CREATE TABLE public.example_table ( id integer UNIQUE ); CREATE TABLE public.foreign_table ( id integer, example_table_id integer, CONSTRAINT ...
Ryan Rapini's user avatar
6 votes
1 answer
16k views

Releasing disk space by using DELETE or TRUNCATE?

More than a week ago, I deleted all the rows in a postgresql table (not via truncate, instead with delete from ...). Select count (*) reveals table rows are now 0. When I now run queries to query ...
Hassan Baig's user avatar
  • 1,979
6 votes
1 answer
2k views

Truncate Table locking sys views

We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions. Is there a convenient work around for this?...
Jamie Marshall's user avatar
5 votes
3 answers
11k views

Speed difference between Drop table and Truncate table in Postgres

I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again. I'm ...
Francis Lewis's user avatar
5 votes
1 answer
19k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
Kent Pawar's user avatar
5 votes
1 answer
2k views

Can TRUNCATE TABLE be overloaded on SQL Server?

I know it may look like a weird question... I have a 3rd party db and services, and I want to monitor it by DML triggers on INSERT, UPDATE and DELETE of each table. But alas, I have a problem when ...
SHR's user avatar
  • 874
5 votes
1 answer
4k views

"TRUNCATE doesn't run ON DELETE trigger" true or false [closed]

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
geeksal's user avatar
  • 151
4 votes
3 answers
21k views

Truncate parent table in Oracle when child table is empty

Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in ORA-02449: unique/primary keys in ...
Isaac Kleinman's user avatar
4 votes
1 answer
3k views

Why do I have to backup a transaction log twice for it to shrink?

This question was attempted twice on experts-exchange with no real answer. I know that I should not shrink the log file - that is not the question. If you feel like answering that one should not do ...
Peter PitLock's user avatar
4 votes
1 answer
1k views

What factors prevent and delay TRUNCATE command?

I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent truncate ...
jrara's user avatar
  • 5,333
4 votes
2 answers
1k views

Which command Deletes and Frees Space

I'm reading through the 98-364 exam book and there's a question I can't quite answer: "What command would you use to remove all records from the Contact table that do not have a country of USA ...
Stuart's user avatar
  • 41
4 votes
1 answer
4k views

Transaction Log is big! But simple recovery mode is chosen!

I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple". The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB). Now i made a ...
frupfrup's user avatar
  • 187
4 votes
1 answer
4k views

Truncate log file mirrored database

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it. ...
xrpapak's user avatar
  • 51
4 votes
2 answers
4k views

Is there a way to "SELECT and TRUNCATE" without losing new data?

I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
Bryan's user avatar
  • 417
4 votes
1 answer
4k views

Huge MSDB database

I have a very large MSDB database and have been working on clearing this out and setting up maintenance tasks - something my predecessor should have done years ago! So far I've managed to truncate ...
U01SFA3's user avatar
  • 53
4 votes
1 answer
7k views

SSIS package blocks itself if uses TRUNCATE

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data: Here the Delete ...
GSerg's user avatar
  • 1,352
3 votes
3 answers
99k views

Truncate tables with dependent foreign key constraints

Hi I'm trying to truncate some tables from my db, but they are related with foreign key constraints so every time I try it SQL Server throws an error like this: Cannot truncate table 'Table' ...
E.Rawrdríguez.Ophanim's user avatar
3 votes
2 answers
606 views

Why is it truncate table, and not simply truncate?

Upon reviewing some of our code a colleague posited that he was annoyed at always having to write TRUNCATE TABLE when there is no real other usage for TRUNCATE. Googling this, I indeed can not find a ...
Reaces's user avatar
  • 2,611
3 votes
4 answers
4k views

SQL Server TRUNCATE TABLE permissions

Is it possible to configure SQL Server user so that he doesn't have access to DDL statements but has access to run TRUNCATE TABLE commands. I know that behind the scenes it's a DDL. I wasn't able to ...
Evaldas Buinauskas's user avatar
3 votes
1 answer
24k views

Is there a way to truncate table that has foreign a key?

Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
IvanP's user avatar
  • 557
3 votes
3 answers
7k views

Can a TRUNCATE or DROP table ever be very time consuming?

A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day. It was a top candidate ...
Jérôme Verstrynge's user avatar
3 votes
3 answers
4k views

Using dynamic query to loop through Db for a truncate statement

Please, any help with my SQL query below will be appreciated. I am not familiar with how to loop through DBs to truncate a table. What I have tried is below: DECLARE @DynamicSQL AS VARCHAR(MAX) = ''; ...
Djoe1's user avatar
  • 111
3 votes
3 answers
267 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million). We're ...
Alex KeySmith's user avatar
3 votes
2 answers
5k views

Add foreign key constraint to an existing table column, referencing a column in a newly created table

Let's say I have one table defined as this CREATE TABLE sales ( agent_id integer references agents(agent_id), sale_date date, amout numeric(10,2) ); Then an ETL process fills ...
Edgar Ramírez Mondragón's user avatar
3 votes
3 answers
2k views

I have run truncate table but it is still showing table occupying space?

I have run truncate table but the Heidi SQL is still showing table occupying space. Database: MYSQL 5.6.26 I have truncated the con_consumer table but heidi is still showing it taking space. Even ...
wizneel's user avatar
  • 85
2 votes
1 answer
10k views

How long will a truncate table take on 252M Rows (170GB data)?

I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and ...
crichavin's user avatar
  • 403
2 votes
3 answers
2k views

SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days? [closed]

I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they ...
DomBat's user avatar
  • 133
2 votes
2 answers
908 views

When to prefere a truncate over a batch-delete?

When I browse DBA-Reactions I understand nearly all the .gifs but with this one I have trouble: https://twitter.com/DBAReactions/status/613840893322338304 I mean truncate obviously doesn't create ...
RayofCommand's user avatar
  • 1,363
2 votes
1 answer
3k views

"Truncate" or "Delete/Vacuum Full" for deleting some of the table rows

I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided ...
mctuna's user avatar
  • 145
2 votes
1 answer
248 views

T-SQL to check whether or not a indexed view can be created on tableX?

After applying my database changes on the test environment, the test team starts testing their processes, one of the things I get is a truncate table error because the table is used in an indexed ...
Marcello Miorelli's user avatar
2 votes
3 answers
2k views

SQL Server : Log File Shrink

We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue. I have ...
Crimsonfox's user avatar