Tag Info

Hot answers tagged

79

No. TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary. Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE ...


32

Testing TRUNCATE then DROP vs just doing the DROP directly shows that the first approach actually has a slight increased logging overhead so may even be mildly counter productive. Looking at the individual log records shows the TRUNCATE ... DROP version is almost identical to the DROP version except has these additional entries. ...


18

This difference only seems to apply when the object is a B+tree. When removing the primary key on the table variable so it is a heap I got the following results 2560 2120 2080 2130 2140 But with the PK I found a similar pattern in my tests as well with typical results below. +--------+--------+---------+-------------------+ | @table | #table | ##table | ...


14

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online. From the point of view of a snapshot isolation transaction, truncate has ...


14

If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically. Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data. What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file ...


11

Other queries. A TRUNCATE statement has to wait until all SELECT statements from that table to complete and as well it must wait until all other transactions that issued INSERT, UPDATE, DELETE, MERGE statements on the table being truncated to commit or rollback. Also other DDL transactions (ALTER TABLE) have to commit or rollback before TRUNCATE can proceed. ...


8

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bug numbers opened for prior versions as well. You can use: CREATE TABLE log_table_new LIKE log_table; RENAME TABLE log_table TO log_table_old, ...


7

According to comments in the source, truncate creates a new, empty storage file, and deletes the old storage file at commit time. (Docs suggest "storage file" is just a file as far as the OS is concerned, but I might be misunderstanding the terminology.) Create a new empty storage file for the relation, and assign it as the relfilenode value. The old ...


7

No, there is no such hint. What you can do is alter table {child_table} disable constraint {fk_constraint_name}; truncate table {parent_table}; alter table {child_table} enable constraint {fk_constraint_name};


6

You can cycle the error log by calling sp_cycle_errorlog and then that will close the current error log and cycle the log extensions. Basically, it'll create a new error log file that SQL Server will be hitting. Then the archived error log(s) can be treated accordingly (delete/move with caution). This will not technically "truncate" the log, it'll just ...


6

It's because TRUNCATE is DDL rather than DML From MSDN: TRUNCATE TABLE [ { database_name .[ schema_name ] . | schema_name . } ] table_name [ ; ] Also, regarding permissions: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the ...


6

TRUNCATE table will take a SCH-M lock on the table. Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the conflicting SCH-M lock has been released.


5

The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes. Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML. The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are: TRUNCATE can change storage parameters (the NEXT parameter), and those are part ...


5

Well this won't happen unless a trigger is firing on the target table, and the error happens there the INSERT does some processing that adds enough data to make it too wide Also, what happens with this please? This can not fail unless there is some processing. INSERT INTO table (col1, col2) SELECT LEFT(column1, 200) ,column2 FROM tmpTable


5

Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation). Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding ...


5

The restrictions on truncating tables include: You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential. This is presumably because truncate is DDL and doesn't do any checks ...


5

This is normal behavior when you truncate table and which involves removal of more than 128 extents as Per Books Online When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both ...


4

You must remember that TRUNCATE TABLE is DDL not DML. Rather than figuring out where in the plumbing of TRUNCATE TABLE it is getting stuck, you may just have to take matters into your own hands by replacing this TRUNCATE TABLE sampledb.datatable; with this CREATE TABLE sampledb.datatablenew LIKE sampledb.datatable; ALTER TABLE sampledb.datatable RENAME ...


4

Any solution proposed, or any solution you could envision for the mater, would suffer from the same issue: coalesce point around the schema modification lock. Whether you do a truncate, or an alter table ...switch, or sp_rename, alter schema whateverver, it doesn't matter. They're all the same solution in disquise. And all will have a point in time when the ...


4

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would: Select the (relatively few) surviving rows into a temporary table. Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer: Optimizing bulk update ...


4

but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen). The recovery mode is FULL (SQL Server 2005). I guess previous users were taking file system backup which in my opinion is not best practice when you have important app running. You can never get point in time ...


3

This is almost certainly a locking issue as Dezso's comment above implies. PostgreSQL is rather fast regarding truncate so the only possible issue I can think of (assuming you don't have major I/O issues) are locks. Truncate involves replacing the table with a stub file and then removing the old file when the transaction commits. It is very fast, and if ...


3

That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations. Specifically for truncate partition: For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions ...


3

Going off of Martin's comment, you could have a second copy of the table that acts as a shadow. First create two schemas to facilitate round robin: CREATE SCHEMA fake AUTHORIZATION dbo; CREATE SCHEMA shadow AUTHORIZATION dbo; Now in the shadow schema create an identical table that you are currently truncating and re-populating: CREATE TABLE ...


3

Ball park - it will have to read 60Gb out of the log file, write 60Gb to the backup file, do some metadata operations for the log truncation itself and (optionally) move what's left of the log file if you shrink the ldf. For your average disk subsystem I'd expect you would be done and dusted in couple of hours, tops.


3

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log. There is no way to prevent SQL Server from writing to the transaction log. This means that a large transaction can still expand the log, if the current size of the log is too small. The ...


2

Do you need point it time recovery? If not then change the database's recovery model to Simple. Does the database have mirroring or replication configured? That can also cause the transaction log to blow up quite large.


2

You seem to be misinterpreting a part of my advice to your previous question: so I can not Truncate and rename the temp_tables since the system should be on all the time. There was no renaming involved. After TRUNCATE you run an INSERT. The only blocking operation is the TRUNCATE. I quote the manual: TRUNCATE acquires an ACCESS EXCLUSIVE lock on ...


2

I can suggest you another approach/solution. You can partition the table and delete partition older than 3 months just dropping them. This is fast and does not impact other inserts. However seems that you need to keep at least one value; can you calculate values to be kept and insert them in another table?


2

I tried to use the SP sp_delete_backuphistory but this filled my MSDB transaction log and almost killed the server... Are there any other ways to clear this and shrink the file without causing any damage? Do it in batches. If your msdb is huge, then after running the script, to release unused space, I would recommend you to shrink your msdb (Yes ...



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