New answers tagged truncate
Microsoft exams (and preparation material) tends to be stupid in some cases. It works best with excluding answers. You have to keep records? So truncate is not an option for you (and creating a new table is typically not the right way to solve it). Delete does free up the space. Of course not the harddisk space but (again typically) the space inside the ...
Not sure of the exact syntax for SQL Server (there's an edit that says that your RDBMS is SQL Server), but I'd do something like this (I'm making the assumption that a TRANSACTION is a - single - command :-). I don't think it's possible in a single line command. (pseudocode) BEGIN TRANSACTION; CREATE TABLE dummy AS SELECT * FROM My_Original_Table WHERE ...
We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table. So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of ...
One reason why it is taking so long could be the AutoGrow settings on the database files and log files. If that is set to a very low value, you might end up with many many, synchronous, auto grow events. You can avoid this by pregrowing the data/log files and/or increasing the AutoGrow settings.
I figured it was the Revovery Model that was left set to FULL. As the article linked in the question says, it is required to change it to simple of bulk: "The recovery model SIMPLE and BULK_LOGGED perform "best" contrary to the recovery model FULL." So, after setting it to simple the process performed within couple of minutes.
are you beeing blocked by another process? you can check that by using sp_whoisactive (see http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx) here you can also see how far your job is, and what the execution plan look like.
Top 50 recent answers are included