TLDR: default log shipping job and maintenance plan are deleting old files from disk but backups are still displayed in SSMS making it very slow to display "Restore" window. How to delete old .trn/.bak correctly?
(Azure virtual machine, Windows Server 2016 Datacenter (10.0), Microsoft SQL Server Web (2017 compatibility level), SMMS 17.3)
(displayed dates are in YYYY-MM-DD or DD-MM-YYYY format with 24h clock)
Managing databases is neither in my skill set, nor in my job description, and yet I got tasked with preparing backups on MS Sql Server so please treat me as layman in this subject.
I decided to create full database backups everyday (copied to external storage of course), keep few of them on server for convenience, delete old ones and then fill holes with transaction logs for point-in-day restore for few days. After googling a bit I did following on test databases:
Created manual full backup as it is required for log shipping - nothing to fail here
Generated default transaction log shipping schedule task with deleting older files. Works nicely, spams .trn every minute and delete old files from disk
Set up Maintenance Plan to create, and delete old, full backups for test databases at midnight - no problem here as well
Let it run from Friday to Monday, both .trn's and .bak's were properly created and deleted so I had backups of two days to play with:
Tried to restore from backups, which caused problems from missing transaction files. Opening Tasks\Restore\Database is very, very slow, SMMS is grinding 100% disk for several minutes for ~10MB of .trn's of empty database. For database without automatic full backup it listed all .trn's ever created (and manual full backup at first place), obviously accompanied by "file not found" errors". For database with daily full backup it, by default, limited result set back to last full backup and restore worked properly, but it still was very slow to just display backup list. Browsing Timeline is also very slow (several minutes of "not responding").
I found script to fetch script history at https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/ (slightly modified to return all backups of database of interest)
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--and
[database_name] = 'test'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
and it includes deleted .trn and .bak files:
aand so on up to this very minute, for 29682 backup points total.
I'd like "Restore" window to be responsive again, and I have two test databases to freely test at. Can I clear backup list safely? How can I make it automatic, to go with my current schedule jobs well?
I have full administration privileges both on db and os, full creative control and plenty of resources left on vm, so hardly any limitations to possible solutions.
