Sql-server – MSSQL Large Delete

sql serversql-server-2005

I have a table within a MSSQL database that contains more than 100 Million records spread over about 100 days worth of data. I need to purge some of this data based on its date which is an indexed field in the table. I've tried doing a DELETE FROM against an individual date but it was taking a long long time to execute and was causing a drop in server performance. Is there a better way of deleting such a large number of records? Some of this data is still required so unfortunately I can't use truncate.

Many Thanks
Nick

Best Answer

I have had the best success when doing similar tasks with the following flow:

Copy the data to keep into a temporary table
Truncate the original table to purge all data
Move everything from the temporary table back into the original table

One major benefit of this is that your indexes will be rebuilt as you put the data back into the original table.

Related Solutions

Sql-server – What can cause long query durations without high resource usage

As you are running SQL 2005 you can take your SQL Profiler data and compare it against the Perfmon data to see if you can see a correlation. This is done by saving your trace data and perfmon data to files using the normal techniques. Then open the SQL Profiler trace within profiler, and then one of the options in the file menu will be Import Performance Data. This will let you select a query and see what the counters were doing at that time (or close to it depending on your perfmon collection interval).

Disk queue spikes are never good. Especially that high. What's the IO that you are pushing to the disk when the queue gets that high? Basically you don't want a disk queue any higher than (2*n) where n in the number of disks in the array. Since you are using a 2 disk RAID 1 n=1 in your case (since you only get the speed of a single disk).

In perfmon there's a counter which is the seconds per read and seconds per write. What do these counters look like when the queries start taking a long time to run. What about normally? (Anything over .02 seconds is bad.) What's the estimated page life expectancy? (Anything under 300 seconds is usually bad, but this can vary.) What's the SQL Server cache hit ratio? (Anything below ~97% is usually bad. I like mine above 99.9%.)

Sql-server – delete a large LDF file in SharePoint

LDF files are the transaction log files for SQL server. I would not delete it directly, but rather..

1. Right click WSS_Content and choose Tasks -> Backup
2. Select backtup type "Transaction log", select destination backup file and click Ok.
3. After backing up the DB, right click WSS_Content and choose Tasks -> Shrink -> Files
4. Select the type log and shrink action (eg release unused space)

If you don't need the transaction log, you can alter your database to use a 'Simple' rather than 'Full' recovery model, which will not keep these large files around. MSDN Link

FAIR WARNING: Always have a backup of your database/data/settings before you start mucking with it.

Best Answer

Related Solutions

Sql-server – What can cause long query durations without high resource usage

Sql-server – delete a large LDF file in SharePoint

Related Topic