Delete old entries from table in MSSQL

delete all rows from table mysql
sql server fastest way to delete records
sql delete multiple rows
delete all rows from table sql
sql delete from multiple tables
delete query in mysql
sql delete all rows
sql delete from select

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:


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!

Delete old entries from table in MSSQL, If you want to remove all rows in a big table, you should use the TRUNCATE TABLE statement which is more efficient than the DELETE statement. SQL DELETE  Here is an Example: If you want to delete entries that are more than 30 days old from some table 'tableName', having column entry 'datetime'. Then following query runs every day which will do required clean-up action.


DELETE FROM TABLE_NAME WHERE date_column < '2018-01-01';

This will delete all data that entered before January 2018

SQL DELETE - Deleting Data in a Table or Multiple Tables, The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because a WHERE clause  Delete All Data in a MySQL Table MySQL is the world’s most popular open source database, recognized for its speed and reliability. This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields.


if you want to delete last 7days data

delete from table_name WHERE date_column >= DATEADD(day,-7, GETDATE())

DELETE (Transact-SQL), The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! Demo Database. Below is a  To remove one or more rows from a table completely, you use the DELETE statement. The following illustrates its syntax: DELETE [ TOP (expression) [ PERCENT ] ] FROM table_name [ WHERE search_condition]; First, you specify the name of the table from which the rows are to be deleted in the FROM clause.


SQL DELETE Statement, I have a large table with millions of historical records. I was asked to remove millions of old records from this table, but this table is accessed by  Assuming that your table has a Primary Key and a column with a timestamp indicating when the record was inserted), you can use a query along the lines of. delete from tableToDeleteFrom where tablePK in (select tablePK from tableToDeleteFrom where someThresholdDate <= @someThresholdDate)


Deleting Historical Data from a Large Highly Concurrent SQL Server , If you are deleting 95% of a table and keeping 5%, it can actually be quicker to move the rows you want to keep into a new table, drop the old  To delete data from multiple tables using a single DELETE statement, you use the DELETE JOIN statement which we will cover in the next tutorial. To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get better performance.


Fastest way to Delete Large Number of Records in SQL Server, Let's take a look at our table and removing some records. How to delete rows with no where clause. The following example deletes all rows from  In MSSQL, you could create a job to run daily which deletes the first 1000 rows of your query. To steal Adam's query - DELETE TOP 1000 FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate() This would be very safe and would get rid of your data in three months or so safely and would them also maintain the size of the db in the future.