use [NPS-LOG]
go
----------------------------------------------
declare @logs_period int = 31
-- how many days should be saved in database
----------------------------------------------
declare @mindate datetime
declare @period_to_delete int
DECLARE @i int = 0
declare @deletion_point date
set @mindate = (SELECT TOP (1) timestamp FROM [NPS-LOG].[dbo].[accounting_data] order by timestamp asc)
print cast(@mindate as date)
set @period_to_delete = datediff(d, @mindate, GETDATE()) - @logs_period
print @period_to_delete
WHILE @i < @period_to_delete
BEGIN
set @deletion_point = dateadd(d, @i, @mindate)
print @deletion_point
delete from [NPS-LOG].[dbo].[accounting_data] where [timestamp] < @deletion_point
checkpoint
dbcc shrinkfile('NPS-LOG_log')
SET @i = @i + 1
END
checkpoint
dbcc shrinkfile ('NPS-LOG', 16, NOTRUNCATE)
checkpoint
dbcc shrinkfile ('NPS-LOG', TRUNCATEONLY)