0

I have a client with a 2 TB database for a pension company. The database contains a lot of big documents.

They want to archive everything that's not from this year. So far no problem - I'll just do a while loop for batches insert and delete and then backup the log after every batch.

After I perform the archiving can I shrink the database? How long can it take if we're talking about maybe 400GB that was moved?

It sounds like a very heavy process for a server like that. I can do it on the weekend but I'm not sure if it'll stop running in time.

1 Answer 1

2

Firstly this needs to pre-mentioned that only shrink files if its actually needed, as you'll cause a lot of index fragmentation which you'll need to clear up afterwards.

With that said, if you're worried about the time it takes to run the entire shrinkfile then you can do it in stages (takes longer overall but technically can be stopped and resumed with a lot more ease), personally I use this to drop it in 2GB stages (also useful on high transactional dbs to reduce contention for the operation to shrink in smaller chunks I've found)

DECLARE @value int = 2048
DECLARE @target int = 1536
SET @value = (@value *1024)-2048

WHILE @value >= (@target*1024)
BEGIN
    DBCC SHRINKFILE(filename ,@value)
    SET @value = @value - 2048
END

Replace

@value with the current size of your file in MB and the

@target with what size you want to shrink down to and

filename with the name of the file you wish to be shrinking

2
  • thank you !very helpful. i did not know you can do that. about the fregmentation im aware of that i dont know if i have enough time to do a rebuild after that as well. and i am worried about the time i can only run thia from friday after noon to sunday morning before work
    – sql_girl
    Apr 12, 2018 at 7:46
  • @winter_sql: shrinks can be interrupted without undoing the work so far, or can explicitly be done in chunks as Ste suggests, so that part can be done in stages if your maintenance windows are not long enough to do it in one. Same for index rebuilding: do as many tables as you can safely in the first maintenance window, continue where you left off in the next. You'll never get an "entirely clean, job done" feeling doing it in parts, but it'll be good enough and far better than not dealing with the matter at all. Unless the fragmentation isn't too bad, in which case you don't have an issue. Apr 12, 2018 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.