Reducing .mdf file size

Question

I have a drive, :d, in my server where I have my .mdf file stored and now all of a sudden I notice that the drive :d has no space left.

When I checked the .mdf file of the database demo1, it is 1.6TB and occupies the entire drive :d. That is an important database and I cannot truncate tables in there.

I checked the database properties and noticed that the initial size of the .mdf is given as 1.6TB by the person who created the database.

How can I reduce the .mdf file size now? I tried shrinking, but it didn't help.

Any idea is helpful.

Answer 1

Offhand, I can think of four things filling up your MDFs:

1. Data; records in tables. If you can archive and delete unneeded records, you can free space.
2. Indices. You can query sys.dm_db_index_usage_stats for information about which indices are rarely used, and consider dropping some. There's a risk that an index that looks safe to delete is part of an important long-running query, so ideally, you'd test your database and typical workload without the index before making a change to production.
3. Fragmentation (really, an extension of #1 and #2). Badly fragmented tables and indices can take up several times as much space, which hurts performance as well as consuming your disks.
4. Free space. Any records or indices you delete are not immediately freed to the file system, they're just made available to other tables and indices. That's a good thing, leave it there! In specific circumstances, it may make sense to reclaim that space (using DBCC SHRINKFILE), but usually only when you've made a major structural change and you do not expect to need that space again.

If you can't delete data, and you can't add disks, in the short run you may be able to buy time by dropping indices and defragmenting tables and indices. Both of those should be part of your regular maintenance, as a DBA. Reviewing indices requires human intelligence, but defragmentation can easily be automated.

LDFs are another matter, and tend to bloat when you have long-running transactions and/or too few backups. If that's also an issue, there are several questions and answers here on DBA.SE you can peruse.