0

Forgive me, but I am very new to being a DBA as I ended up in this role out of necessity :)

I am currently working on reducing the size of several databases. I previously asked a question here about how to accomplish this, and I used the DBCC Shrinkfile command. It worked fine for my first database, as the file size was significantly reduced. However, when I moved on to the second database that needs its file size reduced, the DBCC Shrinkfile query has been running over the weekend and still hasn't finished. Did I do something wrong?

I have tested the DBCC Shrinkfile command on this database before, and even reducing just 10MB took about 10 or 20 minutes. Am I missing something? Is there something I should do beforehand?

Additional information:

  • The size of the database I'm trying to reduce is 1.78TB (I set the query to reduce it to 1TB) with 55% free space.
  • I have set the recovery model to Simple.
  • Link to my previous question
  • SQL Server & SSMS 2017
  • The server is on a VM, 8 core cpu Xeon e5, 24GB ram, and its running on hard disk.
Improve this question
6
  • What kind of hardware you've got? Run a query to see what's the shrinkdatabase status. Try, say, sp_whoisactive or sys.dm_exec_requests.
    – vonPryz
    Jun 12 at 5:07
  • 8 core xeon e5 with 24 GB ram. it is stuck at 56% completion (used the sys.dm_exec_resquests)
    – P5_
    Jun 12 at 6:20
  • That a VM or a physical device? How about storage? Add that info into the question body, do not add a comment. This is not a messageboard, so all relevant info should be edited into the question itself. Comments are ephemeral anyway.
    – vonPryz
    Jun 12 at 6:44
  • yep, updated it. thanks @vonPryz
    – P5_
    Jun 12 at 6:55
  • 1
    FWIW, I read through the rest of your related Posts and there's definitely some bad advice commented on the original one, so here's some facts. You shouldn't need to regularly shrink the database (only as a rare event). If you were able to reduce the table down by 90% then something unusual happened to originally either grow it so large or something changed business-wise to allow you to remove data. Either way, you should proactively ensure that the table doesn't grow so large again, requiring another shrink, which is wasteful. Make sure the growth settings on the database are appropriate.
    – J.D.
    Jun 12 at 12:21

1 Answer 1

Reset to default
2

I'd like to emphasize what J.D. mentions in the comment, that you only what to shrink in very special circumstances. You have discovered one of the reasons - it can take forever, almost to the point to it seems it isn't working.

The comparison between the two systems is partly (but only partly) related to how much data you have in there. Consider adding that to your post. Not the file size, but how much data you have in there. That is worst case scenario how much data that has to be moved from end of file to beginning of file. But even this depends on where in the file you happen to have free space before the shrink.

My guess though is that this database suffers from one of below:

Blocking, where the shrink is being blocked. This is easy to check for (sp_whoisactive or whatever is your preference).

Heap tables. Remember that for each page moved, every nc index has to be updated, for every row on that page. A simple example I had with a clustered table vs a heap table have me 50 times longer shrink time with the heap. You can investigate how many heap table you have and consider if these should be clustered in the first place ot can be made clustered for the shrink process. Of course, of the table is large, then converting it from heap to clustered will take a long time.

LOB pages. Imagine SQL Server moving a lob page. It will have to scan the entire table to see which row(s) were pointing to this page so it can modify that page address for the LOB page. For. Each. Moved. LOB. Page. Let that sink in. Remeber that there are several lob types (varbinary(max), varchar(max), nvarchar(max), xml, geography and geometry). Not many options here, but to export lob data to a different filegroup, do the shrink and then it back again, or something like that.

Improve this answer

Your Answer

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

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