My database has 9556.97MB of space left.

I do have about 2TB free disk space. The database is in full recovery mode and doing full backup everyday, also TLOG backup every 24 minutes.

database property

database autogrowth settings I used to just truncate as I was taught but I came across a blog post which is teaching not to SHRINK:


There is 2TB of free space on the disk where the database resides but it says only 10GB of space is available. Why is this so and what steps should I follow to keep database from running out of space?

  • 2
    What is your question?
    – mustaccio
    Mar 18 '19 at 17:13
  • Possible duplicate of When is it OK to shrink a Database?
    – mustaccio
    Mar 18 '19 at 17:14
  • It really is better to ask why is your database growing then merely attempting shrinking. Why we had a database that grew by 500mb an hour until we discovered the error logs from the app were pouring 1TB+ of useless error messages.
    – clifton_h
    Mar 19 '19 at 1:24

Your question tag indicates SQL Server 2016. If you are on at least SP1, you can take advantage of DATA COMPRESSION.

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.

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'
  • thanks for your reply @Scott Hodgin yes i am Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 Never used DATA COMPRESSION but using Backup compression. i am archiving proactively and also deleting data that doesn't reside in the database . fill factor already set to 100% haven't checked the unused indexes ,going to check them .since i am using standard edition i can do only index reorg and that is running every weekends .
    – md shamim
    Mar 18 '19 at 18:17
  • @mdshamim - you should definitely check out DATA COMPRESSION. You would probably find some serious space savings. Mar 18 '19 at 18:19

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