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

Truncate a table with 17 billion rows in an AG

Logging Extents The amount of log data generated (and thus sent over the network to your other AG nodes) depends on how big the rows are in your 17 billion row table are. TRUNCATE will definitely be ...
Josh Darnell's user avatar
  • 29.3k
28 votes
Accepted

Postgres suggests "Truncate table ... at the same time"

What does it mean to truncate a table at the same time? It means with the same statement. You can truncate more than one tables: TRUNCATE xxxxx, yyyyy RESTART IDENTITY ; More details in Postgres ...
ypercubeᵀᴹ's user avatar
19 votes
Accepted

Truncate a table with 17 billion rows in an AG

Truncate table command truncates rows instantly and does not write deleted rows to transaction log file Usually Truncate table executes instantly and there is no noticeable network traffic between ...
Aleksey Vitsko's user avatar
14 votes
Accepted

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

SQL Server's transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least ...
AMtwo's user avatar
  • 16.2k
12 votes
Accepted

Truncate with where clause

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes. From BOL: -- Syntax for SQL Server and Azure SQL ...
Erik Darling's user avatar
12 votes
Accepted

Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

Truncating a temp table at the end of the stored procedure that creates it seems to cause the space the table uses in tempdb for the data to be released faster than if no truncate statement is used, ...
Paul White's user avatar
  • 85.5k
11 votes

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

Remember that truncate is an efficiently logged operation. Since you are running in Simple Recovery mode and provided your T-log is not truncated, you can get it using undocumented fn_dblog: SELECT [...
Kin Shah's user avatar
  • 62.3k
11 votes
Accepted

Don't RESEED tables on Query Store Clean

Assuming you're using the command ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR to "clean the query store," I decided to see what queries are run behind the scenes when that command executes....
Josh Darnell's user avatar
  • 29.3k
10 votes
Accepted

Releasing disk space by using DELETE or TRUNCATE?

From the docs on VACUUM There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can ...
Evan Carroll's user avatar
  • 63.5k
9 votes
Accepted

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

A millisecond or so. Truncate is O(1) - a pure metadata operation. This is assuming there is no concurrent activity on the table. Truncate will potentially need to wait until existing reads are ...
TomTom's user avatar
  • 4,638
8 votes
Accepted

Truncate tables with dependent foreign key constraints

You cannot truncate a table that has foreign key constraints. I would script your truncate statement to drop the constraints, then truncate the table, and then re-create the constraints. Because ...
Shaulinator's user avatar
  • 3,142
6 votes

Postgres suggests "Truncate table ... at the same time"

1) Cascaded truncates can be dangerous. If you have a lookup table (in this case, I believe XXXX), kiss it good-bye because it's going to be truncated and you'll lose all that data. For example, you ...
codedawg82's user avatar
6 votes

SQL Server TRUNCATE TABLE permissions

You could use this technique by Jonathan Kehayias to create a stored procedure that will allow the user to TRUNCATE a table. As Johathan says: "You can't grant truncate because for whatever ...
Scott Hodgin - Retired's user avatar
6 votes

Using dynamic query to loop through Db for a truncate statement

As Jacob H mentioned in the comments, you'll need to query sys.tables with the appropriate USE statement for each database, or by explicitly using the three-part syntax {database}.sys.tables for ...
Geoff Patterson's user avatar
6 votes
Accepted

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

If I understand the documentation properly: https://www.postgresql.org/docs/current/sql-truncate.html TRUNCATE CASCADE truncates every table that has a foreign key relationship pointing to (...
Lennart - Slava Ukraini's user avatar
6 votes

Speed difference between Drop table and Truncate table in Postgres

DROP & CREATE is slightly more expensive, as it actually deletes rows from some system tables (pg_class, pg_attribute, ...) in addition to removing the physical table files - and later has to ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Truncate all Tables in Database with T-SQL

I'm not sure why you would want to truncate all tables in a database, but, there are several ways to do this. You can use a cursor to loop through sys.tables or INFORMATION_SCHEMA.TABLES: DECLARE @...
HandyD's user avatar
  • 9,972
5 votes

TRUNCATE TABLE statement sometimes hangs

There is a long standing bug in InnoDB that affects both MySQL (#98869) and MariaDB (MDEV-9459) which results in long stalls, proportional to the size of your buffer pool (typically around 1 second ...
Gordan Bobić's user avatar
5 votes
Accepted

Can TRUNCATE TABLE be overloaded on SQL Server?

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like: DELETE FROM X; --remove all rows TRUNCATE TABLE X; --truncate the empty table When you delete from a table, ...
Shaulinator's user avatar
  • 3,142
4 votes

Truncate with where clause

There are three delete methods in sql server: Truncate , Delete , Drop DROP, TRUNCATE are DDL commands (DROP is used to remove objects like tables, columns, constraints,...but not rows) DELETE is a ...
Hadi's user avatar
  • 1,222
4 votes
Accepted

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

Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In ...
Tom V's user avatar
  • 15.7k
4 votes

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

I'm afraid it's not possible to know the last time a table was truncated without setting up something to capture the event upfront. The default trace does not hold information for truncate events and ...
spaghettidba's user avatar
  • 11.3k
4 votes

Truncate tables with dependent foreign key constraints

The two links in your post point to scripts that generate the commands necessary to drop and recreate all your current foreign keys (I haven't tested them, just glanced through for intent). However, ...
RDFozz's user avatar
  • 11.6k
4 votes

Truncating a table with a foreign key constraint - SQL Server

The documentation for TRUNCATE TABLE (Transact-SQL) is fairly clear on this topic. Referencing the Restrictions: You cannot use TRUNCATE TABLE on tables that: Are referenced by a FOREIGN ...
Scott Hodgin - Retired's user avatar
4 votes

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

You can use the NOT VALID modifier when creating a foreign key to stop it verifying existing data. For instance: ALTER TABLE sales ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date) REFERENCES dates ...
David Spillett's user avatar
4 votes

What permissions are needed to truncate a table in PostgreSQL?

You need to have the TRUNCATE privilege to truncate a table (or be a superuser). Since PostgreSQL has no INDEX, EVENT, ALTER or DROP privileges, I suspect that you are using a different software.
Laurenz Albe's user avatar
  • 52.8k
3 votes

MySQL running delete trigger on truncate

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them. That's ...
ypercubeᵀᴹ's user avatar
3 votes
Accepted

Sql Server Transaction logs during large deletes

We don't know why your transaction log backups will fail but we can discuss solutions. He could be concerned about transaction log bloat, when the transaction log would grow so large that it filled ...
Arthur D's user avatar
  • 932
3 votes
Accepted

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

I mean, could [TRUNCATE OR DROP TABLE] block a production environment for several minute like DELETEs can For Microsoft SQL Server, in rare cases yes. Both TRUNCATE and DROP are "metadata only" ...
David Browne - Microsoft's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible