Questions tagged [truncate]

A DDL operation that quickly removes all rows.

Filter by
Sorted by
Tagged with
100
votes
6answers
33k 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 ...
23
votes
3answers
52k 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.
20
votes
2answers
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/...
17
votes
1answer
38k 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?
15
votes
2answers
23k 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....
12
votes
1answer
5k 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 ...
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 ...
10
votes
1answer
3k 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 = ...
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 ?...
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 ...
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. ...
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 ?
5
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 ...
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 ...
5
votes
1answer
17k 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 ...
5
votes
1answer
837 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 ...
4
votes
1answer
2k 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 ...
4
votes
4answers
16k 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], ...
4
votes
1answer
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 ...
4
votes
2answers
581 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 ...
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 ...
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". ...
4
votes
1answer
2k 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 ...
4
votes
1answer
3k 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. ...
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 ...
4
votes
1answer
6k 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 ...
4
votes
1answer
33 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?...
3
votes
3answers
18k 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 ...
3
votes
2answers
577 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 ...
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 ...
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 ?
3
votes
3answers
229 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 ...
3
votes
2answers
7k 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 ...
3
votes
2answers
2k 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 ...
3
votes
1answer
3k 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 ...
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 ...
2
votes
3answers
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 ...
2
votes
3answers
1k 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 ...
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) = ''; ...
2
votes
1answer
2k 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 ...
2
votes
1answer
155 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 ...
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. ...
2
votes
3answers
1k 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 ...
2
votes
2answers
4k 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 ...
2
votes
1answer
718 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 ...
2
votes
2answers
74 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.
2
votes
2answers
749 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
2answers
2k views

SQL Server 2008 - Log file out of control & cant shrink it [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down ...
2
votes
1answer
86 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 ...
2
votes
0answers
41 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)...