On my production SQL Server Enterprise Edition database 2014, I am trying to find why data files (.mdf) growing day by day.

I get the following report From Management Studio: right click on the database> reports > standard reports > disk usage by top tables.

I found some tables contain more than 70 million rows. One table takes more than 13625168 KB - almost 12 GB! What should I do?

enter image description here

Is there away to optimize the size of table even if contains a huge number of rows?

Some data is needed, other data only needed for archiving purposes.


Can I Compress the huge table?


Since you're using SQL Server 2012 Standard, you're not able to take advantage of DATA COMPRESSION. It's an Enterprise feature up until SQL Server 2016 SP1. When you're able to upgrade, you should check out that functionality. 'In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.'

However, there are a few things you can do now to possibly reduce the size of your databases. Look for the tables that are taking up the most space and...

  • Archive (and delete) any data that doesn't have to reside in the database. Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages.
  • If you haven't done so already, consider increasing the fill factor on your indexes to 100%. Having your pages as full as possible can reduce the overall space needed requiring fewer pages in memory. Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages. Check out Does index fill factor affect fragmentation?
  • Try to find unused or redundant indexes. If an index is not being used, it's just taking up space in the database for no reason. Likewise, you may be able to consolidate several redundant indexes into fewer ones. There are numerous free tools and queries available by searching for 'sql server unused indexes'. A personal favorite of mine is sp_BlitzIndex.
| improve this answer | |
  • ,if i am using sql server 2014,could i compression one table ?? – Mike Darwish Sep 28 '17 at 19:42
  • 1
  • 1
    @MikeDarwish - data compression is an Enterprise feature up until SQL Server 2016 SP1 – Scott Hodgin Sep 28 '17 at 19:49
  • ..this is what i am searching about to copression ahuge table , are you able to update your answer and add aline said your able to compress table ..thanks – Mike Darwish Sep 28 '17 at 19:49
  • question updated , i will copy my db to sql server 2014 entrprise edition – Mike Darwish Sep 28 '17 at 19:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.