Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [truncate]

A DDL operation that quickly removes all rows.

1
vote
1answer
63 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
1answer
60 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
0answers
31 views

MSSQL All table data gone

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 ...
0
votes
1answer
27 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
74 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
139 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
0answers
13 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
2answers
37 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
78 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
267 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 ...
0
votes
0answers
117 views

Recovering data into truncated table

I accidentally truncated data from 8 tables. I am new to MySQL and was wondering if someone could help me with step by step instructions to restoring the data. Also, I have no experience with ...
5
votes
1answer
540 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
41 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
513 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) = ''; ...
1
vote
1answer
79 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 ...
0
votes
1answer
2k 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 ...
0
votes
0answers
119 views

MySQL 5.7 Using and Truncating Partitions

I am working on a large statistical database. It's currently several hundred million rows. What I need to do each night is to truncate it and then rebuild the data from the source files because ...
3
votes
4answers
516 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 ...
0
votes
3answers
7k 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' ...
12
votes
1answer
2k 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
1answer
1k 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 ...
2
votes
2answers
2k 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
3answers
838 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
1answer
254 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
1answer
609 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 ---- ----...
3
votes
2answers
6k 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], ...
1
vote
1answer
116 views

Effect of TRUNCATE TABLE different from DELETE on subsequent queries

I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that: 1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc) 2) TRUNCATE is ...
5
votes
1answer
1k 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 ...
2
votes
2answers
428 views

External size of table still not released (as disk space) after issuing truncate command

In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows: Table | Size | External Size ----------------------------------+------...
2
votes
0answers
189 views

Cassandra performance decrease after truncate [closed]

I have a Cassandra cluster with 2 nodes and simple replication strategy. There is a several columnfamilies, the biggest - about 300m records. There were performance issues, so I decided to truncate ...
1
vote
2answers
56 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 ...
0
votes
2answers
2k views

Oracle “insert /*+APPEND*/” into empty table

I have following code in script: execute immediate 'truncate table tab_a drop storage'; insert /*+APPEND*/ into tab_a select (...) I am wondering if this APPEND hint can cause any performance ...
3
votes
1answer
5k 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 ...
2
votes
2answers
3k views

How can I check if my log is truncated?

I have a testdb. the recovery mode is on simple and I am back my system up with veeam. Now how can i check if my log from the testdb was being truncated after the backup or not? I just found some ...
0
votes
1answer
35 views

backup of databases - consistently back up databases with control of log truncates

My question is how can I back up my database by controlling the log truncates? I need to perform a testing with VEBF (Veeam Endpoint Backup FREE). A test case that I have to make now is that I have ...
0
votes
2answers
100 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 ...
3
votes
1answer
130 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 ...
1
vote
3answers
182 views

Sql Server Transaction logs during large deletes

At my work, I am going to be moving millions of records to a different server, and removing them from the source server. My DBA has set up a backup scenario where change backups are taken every 5 ...
7
votes
2answers
4k 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 ?
0
votes
2answers
1k views

MySQL TRUNCATE TABLE blocks queries in other databases

When I run automated tests against my test_development database, I often observe degraded performance for my production users, even though those users aren't accessing the same database as the test ...
2
votes
1answer
465 views

MySQL running delete trigger on truncate

I have a table that has an on delete trigger that updates another table. THe table I'm trying to truncate has 130,000 rows. I have duplicate schema and data on my dev server as well as the client's ...
1
vote
1answer
39 views

Deleting data from some but not all tables

I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up. I want to delete all the ...
1
vote
1answer
71 views

Inserting rows into a truncated table will increase the size?

Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the ...
1
vote
1answer
638 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 ...
4
votes
1answer
669 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 ...
3
votes
0answers
136 views

LOG BACKUP is TRUNCATING in Log Shipping

I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got ...
1
vote
1answer
673 views

Having performance issue for an extract date without time to compare with sysdate,

I need to get all the rows where its 'time' field is equal to sysdate. The thing is I just want to compare dd/mm/yyyy and as my fields are in the date format I'm using select * from myTable where ...
0
votes
1answer
1k views

Truncate/delete tables for non-owner schema

Is it possible to give truncate rights to user A to truncate tables owned by user B? But I should be able to truncate each table of B by A as per wish. Some procedures would be needed I suppose? ...
4
votes
2answers
529 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 ...
0
votes
1answer
200 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 ...