Guidance Appreciated - Safely deleting large number of rows and resizing a DB

Question

Guidance Appreciated - Safely deleting large number of rows and resizing a DB

xxjhxx2

SSC Rookie

Points: 32
More actions
July 16, 2025 at 7:52 am

#4623171
Hey all,
Bit of background — I’ve somehow fallen into the classic “overnight DBA” role over the past few years at work, handling general maintenance, failover testing, etc. I wouldn’t call myself a deep SQL internals expert, so I’d really appreciate some guidance from those who know more about the guts of SQL Server 2022.
I’m currently managing a 3-node HA cluster (auto-sync, auto-sync, manual-async) in production. We’ve recently had a client request their old data be removed — we’re talking about 3TB of data from a 6TB database.
What’s the safest and most effective way to go about deleting that data and shrinking the disk size? I get that proactive disk sizing is a key part of DBA best practices, but in this case we’re planning to scale down from an 8TB disk to a 5TB one, which should give us another ~2 years of growth post-deletion.
- Is there a recommended process for this? Delete the data, rebuild indexes/tables, shrink the DB?
- Would I need to temporarily remove the DB from the Availability Group during the resize, then re-add and resync it?
Any tips or tricks from those who’ve done something similar would be hugely appreciated!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16346 More actions · Answer 1

I'm not going to comment on whether to remove db from HA or not - others with more experience on that will likely comment. But if you have a good downtime window that may be better.

regarding shrinking the database - if you know you are going to fill it within the next 2 years and if you don't need the currently allocated disk space for other purposes I would not shrink it and would leave as is.

regarding the deletion bit. it depends on table sizes, but for high volumes and when possible I advise the following approach.

questions that affect what you do.

do you need to keep a copy of the data being deleted? if so do this in advance of the main deletion.

disable/drop any foreign keys referencing table (make sure to keep scripts to recreate them!!)
create a table (xx_staging) with exact same structure as the main table (including any indexes/partition schemas/functions)
switch out main table to xx_staging (this is a metadata operation taking miliseconds)
drop all indexes on main table (perhaps leaving the clustered index (must do if clustered columnstore)
Insert data to keep from xx_staging back to main table
recreate dropped indexes on main table.
recreate any foreign key if/as required.
drop table xx_staging

start with smaller tables so when you get to the biggest tables you are likely to have enough space on datafile to hold a copy of full big table + data to keep.

if this process would likely increase the size of your datafile even further then I would create a new database/filegroup to keep a copy of the data to keep and change the steps above slightly.

if Tempdatabase is used

disable/drop any foreign keys referencing table (make sure to keep scripts to recreate them!!)
on temp database create a table (xx_staging) with exact same structure as the main table - indexes not required
Insert data to keep from main table to xx_staging
truncate main table
drop all indexes on main table (perhaps leaving the clustered index (must do if clustered columnstore)
Insert data to keep from xx_staging back to main table
recreate dropped indexes on main table.
recreate any foreign key if/as required.
drop table xx_staging

Portes Ysaias SSCrazy Points: 2144 More actions · Answer 2

Hi xxjhxx2

I recommend that you check first whether the client's data is already isolated in its own tables or partitions. That would be lovely if they do, If so, SWITCH and TRUNCATE, done in seconds.

One way could be, that is if you have some additional storage to spare in the meantime, copy the 3TB you're keeping to a new table, drop the original, rename. Do this only if you can take some downtime. Usually faster than deleting half a table and leaves you a clean structure.

If you must delete in place, then do small batches in a loop, indexed WHERE clause, backup your transaction logs every few minutes. One big DELETE will blow the log and drop your sync replicas out of SYNCHRONIZED, which silently disables automatic failover.

Try to do it in this order:

1) delete a small batch

2) do small shrink

3) then rebuild indexes. Rebuilding first just gets undone.

Shrink in small chunks of 5-10GB increments with WAIT_AT_LOW_PRIORITY (new in 2022).

All of these have to be done on the primary replica.

Check for CDC, Change Tracking, or replication on those tables. Any of them multiplies log volume.

You can use this small script to check on your volumes size, database files size, available space in volume and files.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerName],
       DB_NAME() AS [DatabaseName],
       [dovs].[volume_mount_point] VolumeLetter,
       [dovs].[logical_volume_name] LogicalName,
       [dovs].[total_bytes] / 1073741823.9999983 AS [VolumeSize],
       [dovs].[available_bytes] / 1073741823.9999983 AS [VolumeAvailableSpace],
       (CONVERT(NUMERIC(18, 2), [dovs].[available_bytes]) / [dovs].[total_bytes] * 100) [VolumePercentFreeSpace],
       RTRIM([name]) AS [FileSegmentName],
       [groupid] AS [FileGroupId],
       [filename] AS [FileName],
       CAST( / 128.0 AS DECIMAL(10, 2)) AS [FileSizeinMB],
       CAST(FILEPROPERTY([name], 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) AS [FileSpaceUsed],
       CAST( / 128.0 - (FILEPROPERTY([name], 'SpaceUsed') / 128.0) AS DECIMAL(10, 2)) AS [FileAvailableSpace],
       CAST((CAST(FILEPROPERTY([name], 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) / CAST( / 128.0 AS DECIMAL(10, 2)))
            * 100 AS DECIMAL(10, 2)) AS [FilePercentUsed]
FROM [sysfiles] 
    CROSS APPLY [sys].[dm_os_volume_stats](DB_ID(), .[fileid]) [dovs]
ORDER BY [groupid] DESC;

GO

This reply was modified 3 days, 15 hours ago by Portes Ysaias.

Ysaias Portes | Forward Thinkers Consulting
sp_SQLFlightRecorder — open-source SQL Server diagnostic recorder | forwardthinkersconsulting.com