The database for our storefront goes back to 2007. I'd like to shrink our database by first exporting all records before a given date (say, Jan1 2010) to an excel spreadsheet, then removing all records prior to that date.

Is there information that ADNSF has posted regarding using a SQL query that can remove all records from before a date of your choosing? I'd like to know if there's an approved method that doesn't cause problems when records are removed.

Bob Singleton

in MultiStore by (195 points)

1 Answer

0 votes

Hi

First, start by using the standard ASPDNSF Admin monthly maintenance process and only use the method below if this doesn't work for you...

If you are purging records from a live database, the problem you'll face is that the site freezes during the row deletion. If you have many millions of rows (usually in the dbo.Profile table) this can take ages to run.

You'll also have to keep an eye on the size of your transaction log and if it gets too large, you'll need to backup and then truncate it.

What we usually do in cases like this is run a series of delete commands on a table processing around 5,000 to 10,000 rows per delete using a query like this:

delete top(5000) from dbo.Profile where UpdatedOn < '2014-02-01'

Usually we run about 20 of these commands. Look at the transaction log, truncate it if needed, then run the same commands again until we get back 0 rows deleted.

 

by (650 points)
Agree with Webopius approach.

This SQL Script is also handy for seeing all table sizes

 

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )
AS (SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o ON o.object_id = p.object_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name
, t.table_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
,sum(t.tbl_rows) as rows

FROM table_space_usage as t

GROUP BY t.schema_name , t.table_name

ORDER BY used_in_kb desc
...