How to get free space inside my data files??

Question

Post reply

How to get free space inside my data files??

vsamantha35

SSChampion

Points: 11529
More actions
September 1, 2016 at 12:00 am

#312948

Hi Experts,
Have some doubts on SQL Server space management. I am running SQL Server 2012 SP1 Enterprise Edition.
In my environment, I see the database size is growing rapidly and resulting drive getting completely filled. Its almost near to 200MB Free space. So, I looked into the database which tables are taking up more space and see some tables created by developers with suffix as "_markForDelete", "_bakyyyymmdd" etc.. I deleted some of those tables by taking confirmation for the developer team who takes care of releases/hotfixs for our database and was able to get some free within the data file.
My questions, what else can be checked in terms of space management so that I get rid of junk in my database, get some free drive space without having to expand my drives. I mean, we can raise a request for the expansion if it is really required but I am looking basically for the junk cleanup inside the database.
What else I can cleanup inside a database ???
Thanks,
Sam
September 3, 2016 at 1:19 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Hi

What is 200MB as a percentage of your database size ? 10% or 110%?

IMHO 200MB is a small amount to worry about, when it gets to GB or TB, then it might start to be a cause for concern.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

vsamantha35 SSChampion Points: 11529 More actions · Answer 2

200 MB is amount of Free space left over in H:\ drive where all our data files i.e. mdf and ndf's are located. we maintain a separate drive for logs which is 0:\drive.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You probably want to get more drive space in that case.

Databases grow, that's their nature.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vsamantha35 SSChampion Points: 11529 More actions · Answer 4

Hi Gail,

I understand what you are sawing. What I am looking for is, is there anything we can check inside the database through which we can get some free space, like dropping unwanted tables, unused indexes, shrinking files where currently free space is more than 50%,DBCC CLEANTABLE to reclaim space from dropped variable-length columns in tables or indexed views., Rebuilding high fragmented indexes etc.. I am checking on those lines.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Don't shrink your DB. No need. SQL will reuse empty space within the file.

You can delete data that isn't needed (don't drop indexes, they may be needed for acceptable query performance), or add drive space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass