2

Yes, fully aware of Paul's article, and "DON'T", et al...

This is SQL 2014 Enterprise - Build 12.0.5538 - SP2 CU3 - iron - 2x8 1TB RAM - all SSD.

There is an unused FILESTREAM data file:

file_id  Size in MB type_desc   state_desc  is_percent_growth   growth  Growth in MB
65537          0    FILESTREAM  ONLINE            0               0        0

A data migration by a.n.other Team inadvertently filled the entire data drive. Adding another set of RAID-10 disks to the existing volume would take two weeks (Hosting Vendor doesn't move very fast). As it is full, SQL Monitor is showing the space as full and alerting to that effect.

Aim: bring the data file down from 2.1TB to at least 20% less, to stop the nag-alert, as that's getting Support's attention, Hosting and internal, and we would like to stop those needless investigations.

Problem:

So we can stop the SHRINKFILE if we need to, we nibble away in 5GB chunks. The file is at 2135040MB - still too high to shut the Alerts up. 106GB Free on the 2.18TB drive.

The target is 2129920MB for the current chunk.

The Shink ==> Files SSMS Dialog shows the minimum is 1391374MB - we went nuclear on garbage-collection and post-migration truncations.

Repeatable road-block:

 2018-03-05 16:21:48.730 - Started: Original Size 2135040 MB
 2018-03-05 16:21:48.730 - Shrink: to 2129920 MB
File ID 1 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 2018-03-05 16:22:11.250 - Ended: Shrink failed to run - see Messages

The is no such "...another process...", neither, obviously from SSMS Shrink's perspective, is it "...empty." In fact, this was run on as "silent" a box as we can get - just two DELAY pollers, and nothing, but nothing else running according to sp_whoisactive.

I tried adding some space and retrying. I had to add MANY MB, not just a few, as was suggested. The first 5GB SHRINKFILE went OK, then the above error.

Can anyone assist, please? (Seriously hope it's something obvious to a "real" DBA) With thanks.

  • How does one inadvertently fill drives? Can we “inadvertently” dismount and mount the data files elsewhere? Do these objects need to be here? Can we not remove them? – clifton_h Mar 6 '18 at 0:54
  • An expression conveying kindness, rather than what one could have derogatorily said... No space on any drive on that server to detach/attach. They WERE needed during migration, hence the nuking post-mortem. Removal of tables isn't the issue. DBCC's refusal to shrink the data file according to need, is. – SAinCA Mar 6 '18 at 1:10
  • Do you have the ability to set the recovery model to SIMPLE by chance? If so, perform two CHECKPOINTs for log wrap around, set to SIMPLE, and then perform a shrink. This should allow you to drop it significantly. – PicoDeGallo Mar 6 '18 at 4:22
  • @PicoDeGallo - Changing from FULL to SIMPLE doesn't help shrink data files. That said, the OP should see if shrinking the log file would provide enough room to move other files around, if they haven't already. – RDFozz Mar 6 '18 at 16:38
  • I assume restarting the server is either not an option, or has already been tried? Have you tried adding 500 MB to the file and then shrinking by 5GB again? (It might be slow, but if it would work...). Also, if there are any other files on the drive that could be moved, move those elsewhere. – RDFozz Mar 6 '18 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.