Questions tagged [truncate]
An operation that removes all rows in a table without causing any triggered action
113
questions
1
vote
1
answer
1k
views
What permissions are needed to truncate a table in PostgreSQL?
I’ve just started with a web host which supports PostgreSQL.
When setting up a database user, I have the choice of the following privileges:
■ ALTER ■ CREATE ■ DELETE
■ EXECUTE ■ DROP ■ EVENT
■ INDEX ■...
1
vote
1
answer
434
views
Need a PL/SQL code to drop many partition in 1 single SQL statements
I have a requirement where we need to drop historical partitions which are not dropped .
Requirement is dropping multiple partitions in one step instead of dropping one by one.
like
ALTER TABLE sales ...
0
votes
0
answers
191
views
Truncate table takes too long waiting db file async i/o submit
I've got Oracle 12c data warehouse database on oracle Linux 7.3, and there's job that truncating one table. Table has size of 5GB
And always truncate takes about hour with waiting for "db file ...
0
votes
2
answers
1k
views
Guaranteed rollback in SSIS
I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in ...
1
vote
0
answers
125
views
MySQL : Huge table truncation in replication environment
We have 2 tables of size 186G and 63G that are no longer in use and needs to be truncated from the database.
I cannot directly run the truncate table statement on the master DB as it has 14 replicas ...
1
vote
1
answer
626
views
Long TRUNCATE operations
On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds.
Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds.
It's an ...
1
vote
1
answer
599
views
truncate table in mysql 8.0 is slower than older version
I have few hundreds of tests which use mysql. I was using Mysql 5.7 (percona) and I changed it to Mysql 8.0 . My problem is that before every test I truncate ALL my tables. This was fine with 5.7 but ...
0
votes
0
answers
815
views
Data truncated after importing a MySQL 5.7 dump into MySQL 8
I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04).
Half way through the import exits and returns the following error:
ERROR 1265 (01000) at line ...
2
votes
0
answers
1k
views
How to disable TRUNCATE on whole database or for particular table?
Is it possible to disable TRUNCATE operation on the entire PostgreSQL database, or on a specific table?
1
vote
1
answer
4k
views
SQL Server BULK IMPORT Truncation error
I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file:
UID,...
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 ...
0
votes
1
answer
1k
views
Create table using select statement Truncated incorrect datetime value:
I have looked up different answers on similar question before posting it here.
So i have a table with user id and date column
I would like to store the results of select query to a temporary table.
...
0
votes
0
answers
56
views
What EventClass is a TRUNCATE?
Trying to query why all records are being deleted from a table, nothing comes up with eventclass = 47 (delete) - perhaps its a truncate so I want to query it in the trace file thanks
1
vote
1
answer
808
views
Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?
If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?
1
vote
0
answers
2k
views
Cannot truncate a table referenced in a foreign key constraint even with no child data?
With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
1
vote
0
answers
176
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
1
answer
178
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
0
answers
2k
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 (...
2
votes
1
answer
1k
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 ...
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?...
2
votes
2
answers
836
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
1
answer
887
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 (...
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 ...
0
votes
2
answers
18k
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 ...
5
votes
3
answers
10k
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 ...
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 ...
2
votes
1
answer
2k
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 ...
0
votes
1
answer
331
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 ...
2
votes
1
answer
412
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
1
answer
42
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
1
answer
2k
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
1
answer
7k
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 ...
2
votes
0
answers
141
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)...
0
votes
2
answers
210
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
2
answers
820
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 ...
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 ...
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 ...
1
vote
0
answers
443
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
3
answers
3k
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) = '';
...
2
votes
1
answer
486
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 ...
44
votes
1
answer
80k
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 ...
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 ...
3
votes
3
answers
96k
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' ...
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 ...
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 ...
21
votes
2
answers
21k
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 &...
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 ...
1
vote
1
answer
1k
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
1
answer
2k
views
Audit all does not capture truncate
I want to capture truncate and drop operations on my table.
So i executed the following:
SQL> select name, value from v$parameter where name like '%aud%';
NAME VALUE
---- ----...
9
votes
4
answers
51k
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], ...