September 20, 2017 at 11:41 am
I was running out of space so I ended up deleting some records from one of the biggest table I had. The table was about 175 GB in size and now it is 31 GB in size yet I still see the size of the DB is still what it was before I deleted records. Do I have to resize the mdf file or shrink? I am not sure what to do? Can someone give me some suggestions?
September 20, 2017 at 11:52 am
Deleting records does not automatically shrink the mdf file, you have to run a shrink operation to do that. Do you actually need to free up the disk space?
September 20, 2017 at 12:02 pm
Yes, this is the prod environment running on a physical machine so adding space on the existing drive isn't an option and as of now, I have 2GB left.
September 20, 2017 at 12:14 pm
ZZartin - Wednesday, September 20, 2017 11:52 AMDeleting records does not automatically shrink the mdf file, you have to run a shrink operation to do that. Do you actually need to free up the disk space?
Like ZZartin asked, do you need to free up space on the drive? Is another option available, such as having the SAN administrator extend the volume? The reason we're asking is that yes, you'll have to do a shrink on the MDF to yield space back to the OS, but it comes with a cost. When you shrink a data file, the indexes generally become fragmented to 99%. If you rebuild them, you'll end up with unused space again. During an index rebuild, the table is generally offline unless you have Enterprise Edition and specify an online index rebuild, which can lead to more unused space. If it sounds like a catch-22, then you're reading it properly. Oh, and index rebuilds are logged, so watch the free space on the log drive.
You should ask yourself if the data grows to this size, will it need to do so again? If so, you'll find yourself in the exact same situation again. The database will (if the DBA allows it) grow to store the data it needs to store. A periodic purge and yield of space isn't a long-term solution, but it can be used to buy you time.
The big question to ask yourself is why the table was so large if you didn't really need the data. That's an assumption on my part because you were able to delete enough rows to take it from 175 GB down to 31 GB - a net gain of 144 GB, which I would not call trivial. If you truly don't need the data, why collect it? If you need it for a period of time, say, 3 months and won't need it after that, you could write a procedure to delete everything older than 3 months and schedule a job to do it for you. Running it daily would minimize the impact of a single run.
September 20, 2017 at 12:21 pm
Keep in mind if you shrink the MDF, you can cause index fragmentation. You may need to rebuild indexes, which means space is needed in the files.
SQL Server manages space inside of the MDF and should always have some space available for data growth as well as index maintenance.
September 20, 2017 at 12:31 pm
You can shrink the database to reclaim space. But be SURE to leave some extra space. FAR MORE IMPORTANT is that you MUST REBUILD EVERY INDEX IN THE DATABASE WHEN YOU ARE DONE!!!!!! Shrinking will leave EVERY index 99+% fragmented.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 20, 2017 at 2:41 pm
newdba2017 - Wednesday, September 20, 2017 12:02 PMYes, this is the prod environment running on a physical machine so adding space on the existing drive isn't an option and as of now, I have 2GB left.
You probably want to go get your hands on a larger physical drive then, so that you can start planning on how you'll accommodate additional growth. Install that new drive as soon as possible, and then make plans to move the MDF file. I presume that the log file (.LDF) is on a separate drive. If not, it would be a good time to leave it where it is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 21, 2017 at 5:31 am
sgmunson - Wednesday, September 20, 2017 2:41 PMnewdba2017 - Wednesday, September 20, 2017 12:02 PMYes, this is the prod environment running on a physical machine so adding space on the existing drive isn't an option and as of now, I have 2GB left.You probably want to go get your hands on a larger physical drive then, so that you can start planning on how you'll accommodate additional growth. Install that new drive as soon as possible, and then make plans to move the MDF file. I presume that the log file (.LDF) is on a separate drive. If not, it would be a good time to leave it where it is.
SQL server anyways re-uses the freed space as it needs more space ... and as put by others too , shrinking causes fragmentation .
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply