Delete old entries from table in MSSQL

Question

a table in my MSSQL db is getting over 500MB. I would like to delete the x last entries so the table size is only 100MB. This should be a task which runs once a week. How could I do this?

Example:

Table before deleting the old entries:

Table after deleting the old entries:

Answer 1

You can use DATALENGTH to get the size of the data in a particular column. With a window function, you can sum up a running total of DATALENGTH values. Then you can delete all records in a table that push you past a desired max table size. Here's an example:

-- Sample table with a VARBINARY(MAX) column
CREATE TABLE tmp (id INT IDENTITY(1,1) PRIMARY KEY, col VARBINARY(MAX))

-- Insert sample data - 20 bytes per row.
;WITH cte AS 
(
    SELECT 1 AS rn, HASHBYTES('sha1', 'somerandomtext') t
    UNION all 
    SELECT rn + 1, HASHBYTES('sha1', 'somerandomtext')
    FROM cte
    WHERE rn< 5000  
)
INSERT INTO tmp (col)
SELECT t FROM cte
OPTION (maxrecursion 0)

-- @total_bytes is the desired size of the table after the delete statement
DECLARE @total_bytes int = 200

-- Use the SUM window function to get a running total of the DATALENGTH
-- of the VARBINARY field, and delete when the running total exceeds
-- the desired table size.
-- You can order the window function however you want to delete rows
-- in the correct sequence.
DELETE t
FROM tmp t
INNER JOIN
(
    SELECT id, SUM(DATALENGTH(col)) OVER (ORDER BY id) total_size
    FROM tmp
)sq ON t.id = sq.id AND sq.total_size > @total_bytes

Now check what's left in tmp: 10 rows, and the total size of the "col" column matches the 200 byte size specified in the @total_bytes variable.

UPDATE: Here's an example using your sample data:

CREATE TABLE tmp (id INT PRIMARY KEY, contact VARCHAR(100), country VARCHAR(25))
GO

INSERT INTO tmp VALUES 
(1, 'Maria Anders', 'Germany'),
(2, 'Francisco Chang', 'Mexico'),
(3, 'Roland Mendel', 'Austria'),
(4, 'Helen Bennett', 'UK'),
(5, 'Yoshi Tannamuri', 'Canada'),
(6, 'Giovanni Rovelli', 'Italy')
GO

-- @total_bytes is the desired size of the table after the delete statement
DECLARE @total_bytes INT = 40

-- Use the SUM window function to get a running total of the DATALENGTH
-- of the VARBINARY field, and delete when the running total exceeds
-- the desired table size.
-- You can order the window function however you want to delete rows
-- in the correct sequence.
DELETE t
FROM tmp t
INNER JOIN
(
    SELECT id, SUM(DATALENGTH(contact)) OVER (ORDER BY id)
        + SUM(DATALENGTH(country)) OVER (ORDER BY id) total_size
    FROM tmp
)sq ON t.id = sq.id AND sq.total_size > @total_bytes

SELECT * FROM tmp -- 2 rows left!