Questions tagged [truncate]

A DDL operation that quickly removes all rows.

Filter by
Sorted by
Tagged with
1
vote
1answer
470 views

drop table causes ORA-08103 with another one

In my process I have 2 tables. target table, load table. The target table holds the current data, and a lot of queries are fired against it. When new data coming we use a FULL LOAD and the goal ...
0
votes
0answers
11 views

DDL grant for truncate/drop privilege on table to a user

I am trying to provide drop/truncate privilege on certain table to a certain user. I know that i cant grant him like 'DROP ANY TABLE' to user, is there any DDL grant for this type of issue?
1
vote
0answers
39 views

Stop MySql InnoDB truncate on reboot

How can I stop a truncate of a MySql innodb? When I restart the database it tries to continue the truncating which leads to the problem of the log group capacity exceeding. To increase this limit it ...
1
vote
2answers
92 views

Dropping columns from a table, truncating it and then re-inserting data as before into the table doesn't reduce space occupied

I dropped a few (decimal) columns from my table. Truncated the table and then re-inserted the data as before, barring the dropped columns. However I don't notice any perceptible difference in the ...
100
votes
6answers
34k 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 ...
0
votes
1answer
403 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
1
vote
0answers
47 views

How to truncate a table without locking it?

I have a SYSTEM's table AUD$ in ORACLE's database that is constantly being populated (whenever a DB user logs in to DB - a records is written to AUD$). The table AUD$ has like 100 million records (...
1
vote
1answer
58 views

ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?

I have a database (SQL Server 2012) that is used mainly for ETL of data. Every hour SSIS packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data ...
1
vote
1answer
102 views

Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail ...
4
votes
1answer
34 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?...
2
votes
2answers
78 views

Backup a single table

I need to Truncate a table in Sql server which containing around 250GB data but before truncating i need to take backup of this,it may to csv file or other mode. What is best way to do it.
0
votes
1answer
112 views

Truncate Table With Partitions - aligned/ non-aligned indexes

SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table: TRUNCATE TABLE < tablename > WITH (...
0
votes
1answer
62 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 ...
7
votes
2answers
6k 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 ?
-1
votes
2answers
3k views

Truncate all Tables in Database with T-SQL

Is there a way to truncate all tables in a database without using sp_MSForEachTable in TSQL Language? Background: not my call, DBA company policy does not allow sp_MSForEachTable. EXEC ...
0
votes
2answers
1k 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 ...
1
vote
1answer
243 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 ...
0
votes
2answers
146 views

What's the most efficient way to reclaim unused DB hard drive space?

Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having. We have a database that unintentionally ballooned to 300+ GB. We have turned off the option ...
1
vote
1answer
464 views

Performance Issue after Truncate and Load of a table in Oracle [closed]

One of our clients has migrated from old to new (larger size/resources) servers. A process (X) is taking 4 hours extra whenever we are doing a truncate and full load of a specific table. If we do not ...
4
votes
4answers
17k 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], ...
5
votes
1answer
2k 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 ...
0
votes
1answer
154 views

Don't RESEED tables on Query Store Clean

We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-( Do you ...
1
vote
3answers
108 views

Sql Script takes much longer than estimated

In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly: Select * into ...
2
votes
1answer
93 views

All data got deleted or truncated- What happened?

What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate or ...
0
votes
1answer
32 views

Repeatedly emptying a table, keeping the table itself [closed]

I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?
0
votes
1answer
387 views

Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

Setup: We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users. At the start of the process, .NET application creates a set of temp tables during the ...
0
votes
1answer
1k views

Truncating a table with a foreign key constraint - SQL Server

I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure: Attendance AttendanceID TimeTableID AttendanceDate UserName StudentAttendance ...
8
votes
4answers
26k 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 ?...
2
votes
0answers
42 views

MySQL Queries Truncate Causing Connection Spikes

We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits)...
1
vote
3answers
27k 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' ...
0
votes
2answers
44 views

DBCC Shrinkfile after full backup restoration but before differential restoration

My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to ...
0
votes
2answers
189 views

Can't empty all my database in SQL Server [duplicate]

In my SQL Server I tried to truncate all tables, but there have some constraint. So I running this EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- Disable all constraints for ...
2
votes
3answers
2k 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 ...
5
votes
1answer
869 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 ...
1
vote
0answers
49 views

Adding IF Exist to truncate statement while looping through DBs

Please, I am trying to add if exists statement to the code below such that it only print truncate statements on all DBs where the table= dbo.log only exist. (SQL Server 2016) IF (EXISTS (SELECT * ...
2
votes
3answers
1k 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) = ''; ...
7
votes
6answers
16k 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. ...
1
vote
1answer
138 views

Will TRUNCATE stop the increase of the DB2 database size?

we are periodically doing the TRUNCATE of some large tables in database. After that in order to release disk space we are doing the command: alter tablespace MAXDATA reduce max and everything works ...
3
votes
1answer
17k 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 ?
7
votes
1answer
12k 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 ...
4
votes
1answer
8k 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 ...
3
votes
4answers
1k 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 ...
12
votes
1answer
4k 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 ...
15
votes
2answers
24k 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....
2
votes
1answer
3k 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 ...
23
votes
3answers
53k 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.
4
votes
1answer
1k 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 ...
8
votes
1answer
20k 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 ...
4
votes
2answers
5k 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 "YYYYY". ...
2
votes
2answers
14k views

Oracle: Truncate large table takes forever

Problem I'm truncating a very large table ( 210+ million rows ). It's been hours since it started and I have no idea when it will be done. There're several tables like that, that I want to truncate. ...