November 10, 2023 at 1:00 pm
Its managed SQL Server Business critical, with the latest version, and MS automated management of Log file.
The issue here is there is purge and archival of data creating lots of free space within the files , but it needs a manual cleanup by running dbcc shrinkfile which is irksome !
if they automated Always ON etc in Business critical, this should be a piece of cake!
The first thing I'd do, in that case, is drop the none clustered indexes on your 3 or 4 largest tables, rebuild the clustered index on those to move them to a new file group/file (1 for each table for future planning on space maintenance), and then try a shrink with the truncate option. Don't forget to rebuild your non-clustered indexes for each table on the same file group as the table they belong to. And exception there will be any indexes that don't fragment over time.
A lot of this will go to hell in a handbasket if the table contains LOBs that went out of row or any row overflows.
I've also not done any work in Azure regardless of type so I don't know if you actually CAN even add file groups.
The other thing is that none of the dmvs or other reporting sources are updated until DBCC SHRINK(file or database) actually completes. If you interrupt it, that won't fix that problem. That's why you need to shrink in smaller chunks... so that you can actually see some progress after each DBCC SHRINKxxxxx completes.
And I never use the shrink database option because it offers a whole lot less control over things and does things by % rather than specified size.
As for having a bit of a fit over the "if they yada, yada, yada", it's a complete waste of time and not worth the rise in blood pressure or having a stroke over. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply