To summarize the answers here and to inject my own advice:
1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.
2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.
3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.
4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.
If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.
Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Why Does the Transaction Log Keep Growing or Run Out of Space?
Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.
If you want to shrink the data file by 10GB, look at the size it is now, and then use DBCC SHRINKFILE
(not DBCC SHRINKDATABASE
) and set a size 10GB smaller than that. Look at the size
column from sp_helpfile
- let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:
DBCC SHRINKFILE(filename, 31000); -- 31,000 MB
You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).
Best Answer
With SQL Server transactional replication, data is replicated at the table level, not the database itself. So you can make whatever changes you want to the database. The question is why you want to shrink the database in the first place. Check out ShouldIShrinkMyDatabase.com, and make sure you click through to the linked article. – AMtwo May 21 '20 at 11:20