Delete data from table in chunks without getting out of disk space due to transaction log

Today I found myself trying to delete million of rows from a table with over 200.000.000 rows. A simple Delete From tbData Where ID < 100000000 resulted in a full disk space and a huge transaction log file. The solution came from this post:

deleteMore:
Delete TOP(10000) tbData Where CDRID < 100000000
IF @@ROWCOUNT != 0
    goto deleteMore;

 The TOP(10000) is up to you and you should adjust it to your data. For me the optimal value was 500000.

Comments

Post a Comment

Popular posts from this blog

Freeze panes in BO Webi report like Excel

Image
Since the design of my first BO report a need kept coming up. The need for freeze panes. When there is a table that doesn't fit in the screen and you start scrolling, the moment the headers disappear you lose track of your data and start scrolling up and down, left and right again and again to see those headers. Well here it is what you need to do to add freeze pane capabilities to your report. For this example we have the crosstab table of the screenshots below: Headers disappeared The logic is quite simple. With the help of javascript we are going to move headers accordingly to the user's scrolling so as the user thinks the headers are pinned to the screen while the body of the table is moving. The problem is that if we see the structure of the crosstab it is actually an html table which means that we can not move header cells independently from body cells. HTML structure of the crosstab So we have to break the crosstab down to three independent...
Read more

IIS "Access to the path 'C:\inetpub\wwwroot...' is denied."

Image
When a website in IIS gives you the following yellow screen of death with "Access to the path 'C:\inetpub\wwwroot...' is denied." this means that it is a permission thing. The account running your application pool doesn’t have permission to access this path. This will usually happen when you run your website under a different application pool other than the DefaultAppPool. When a new application pool is created, a new virtual account is created in Windows and it is this account that doesn’t have permission. To solve this issue: In Windows Explorer, right click on the folder you have your website files; in my case C:\inetpub\wwwroot\dnndev8.me\, and select Properties Go to tab “Security” Click “Edit” Click the “Add” button In the “Select Users or Group” window click “Locations” and select your local computer Then in the “Enter the object names to select” textbox write “IIS AppPool\dnndev8.me” without the double quotes. My app pool name was dnndev8.me...
Read more

ASP.NET entity datasource control error - The metadata specified in the connection string could not be loaded. Consider rebuilding the web project to build assemblies that may contain metadata.The following error(s) occurred. The provider did not return a Provider Manifest instance.

Image
When you add an entity datasource control and you try to configure it you might get the following error: The metadata specified in the connection string could not be loaded. Consider rebuilding the web project to build assemblies that may contain metadata.The following error(s) occurred. The provider did not return a Provider Manifest instance. Error trying to configure Entity Datasource control In order to fix this you have to open your edmx file with notepad, locate ProviderManifestToken and change it's value from 2012 to 2008 as shown in the picture below. Save, rebuild in Visual Studio and you are done.
Read more