DBCC ShrinkDatabase is increasing my file size

Question

Yesterday, I asked a question about how to reduce the size of a database. The community suggested using the command dbcc shrinkdatabase. I followed their advice and ran the command for over 24 hours. However, I encountered an error because my hard disk drive was full. Instead of decreasing the file size, the dbcc shrinkdatabase command seemed to be increasing it (see photo links). Moreover, the original free space in the database is now filled up, even though no additional data was added. Did I make a mistake?

This is the exact query I ran: DBCC SHRINKDATABASE (<DB>,1); GO changed it to DBCC SHRINKDATABASE (<DB>,10); GO after I ran out of space (currently still running, nad have not finished. Stopped in the middle)

Please note:

• I am aware that this will fragment my Indexes, and I am fine with that
• This is a Dev environment, no additional data goes in nor out
• I am running SQL Server and SSMS 2017

Original Free Space

After running the query

Can someone please help me. Is there something I did wrong? Should I change the query?

Answer 1

Moving data pages to beginning of the file is logged. If you aren't in simple recovery model, then the ldf file need to be able to accommodate those log records. this is what happened to you. Expected.

As an aside, we tend to suggest using SHRINKFILE instead of SHRINKDATABASE, since you get better control of what you are doing. Wouldn't help in this case, but as an FYI.

So you have about 170GB (used space in data file) that need to be moved to the beginning of the file. That will take a while. Some thing makes it take a very very long time (LOB pages, heap table, waiting for locks). Consider whether it is possible to have the database in simple recovery model (talk to your DBA, it will affect backup routines), and also do shrink in smaller portions.