-3

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:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

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?

András Váczi
  • 31,778
  • 13
  • 102
  • 151
md shamim
  • 11
  • 1

1 Answers1

2

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'
Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52