shrink data file with low priority

Question

shrink data file with low priority

sqlguy80

SSCrazy

Points: 2763
More actions
November 6, 2023 at 6:41 pm

#4322474

so total size of DB on Azure is 1 TB, but when I add all tables ( Data file ) and log file the total size is 100 GB.
So I need to reclaim the 900 GB ( release storage to the OS ) . MS recommends running DBCC shrinkfile with wait at low priority
DBCC SHRINKDATABASE (3, 20, NOTRUNCATE) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF)
I would need to shrink it in 100 GB's multiple times, ( from 1TB to 900 GB , then to 800 GB and so on ) so it completes fast and does not just keep sitting there doing nothing, any ideas> also please send script for the same, The log file space mangement is automatic on azure MI Business critical, but data file needs baby sitting!
ofc, I will rebuild IX's after .

Viewing 8 posts - 1 through 7 (of 7 total)

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

frederico_fonseca SSCoach Points: 16018 More actions · Answer 1

you were given options and directions on your prior post - what did you do with it and if you did try it what was the result?

https://www.sqlservercentral.com/forums/topic/shrinkfile-option-not-working

and shrinkdatabase is not the same as shrinkfile

sqlguy80 SSCrazy Points: 2763 More actions · Answer 2

Thanks Fredco.

dbcc shrinkfile low priority, may be I need a script to reduce the size 100 GB at a time from 3.2 TB to 1 TB.

Secondly, Log file shows 260 GB, how to reduce that ?

Thirdly, This is Microsoft SQL MI , Business critical. can we do this or this is normal / not needed ?

Thanks

Jeffrey Williams SSC Guru Points: 90253 More actions · Answer 3

Or - you could add a file and use EMPTYFILE on the original file. Once the original file is empty it can then be removed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Removed... I somehow duplicated the post below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

Jeffrey Williams wrote:

Or - you could add a file and use EMPTYFILE on the original file. Once the original file is empty it can then be removed.

Have you tried this when the original file is the original MDF on the PRIMARY filegroup?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlguy80 SSCrazy Points: 2763 More actions · Answer 6

These are my file names and sizes.

Please send me scripts so I can shrink file in smaller increments ( from 3 TB to 2.9 TB to 2.8 TB etc and so on then truncate only?)

name space_used_mb space_allocated_mb

data 947479.000000 3039486.812500

log 623.718750 260040.000000

DBCC SHRINKFILE ('data', 2145728) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

DBCC SHRINKFILE ('data', TRUNCATEONLY);

Thanks in Advance

frederico_fonseca SSCoach Points: 16018 More actions · Answer 7

you already have the sql to execute - you just need to copy and paste the 2 lines and reduce the size accordingly yourself