New answers tagged delete
0
Looking at the query I am concerned with the number of rows being deleted.
Which is better ?
Deleting the 1,000,000 million rows
Creating a table with 200,000 rows
Look at Deleting the 1,000,000 million rows
It takes a long time
Table remains the same size
You must run OPTIMIZE TABLE to reclaim the space
Look at Creating a table with 200,000 rows
...
2
Another good way to do this is to copy 0.2 mln records to another table and drop the first table with 1.2 mln.
0
how they could find out who deleted some data in their SQL Server database
Although this is answered, wanted to add that SQL Server has a default trace enabled and it can be used to find out who dropped/altered the objects.
Object events
Object events include: Object Altered, Object Created and Object Deleted
note: SQL Server by default has 5 trace ...
0
If database is in full recovery mode or if you have transaction log backups you can try to read these using third party log readers.
You can try ApexSQL Log (premium but has a free trial) or SQL Log Rescue (free but sql 2000 only).
0
Perhaps the DELETE JOIN would be preferable
DELETE A.* FROM table1 A INNER JOIN table2 b
ON A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;
I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery
...
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?
Top 50 recent answers are included
