-1

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.

Improve this question
13
  • 1
    Have you reviewed existing questions about shrinking MDFs, here on DBA.SE? – Jon of All Trades Jun 10 '19 at 16:48
  • Yes.Sine the initial size is set as 1.6TB ,the minimum shrink size is also 1.6TB.I want o reduce the initial size so that i can shrink it to a smaller size > inital size. – user9516827 Jun 10 '19 at 16:54
  • DBCC SHRINKFILE will reduce the size of an MDF, if there's free space available to release. What exactly was the outcome? Is there free space in the file? If not, you'll need to delete records, drop indices, compact tables, etc. Is that the part with which you need help? – Jon of All Trades Jun 10 '19 at 16:58
  • 2
    @user9516827 - Have a look at this post – Scott Hodgin Jun 10 '19 at 18:30
  • 1
    There is not such as initial size recorded by SQL Server. The GUI show that, but it is wrong, and the word "initial" has been removed in SSMS 18 (per my request :-) ). So don't let that confuse you. Just do the shrink, and you can try using TRUNCATEONY first and see if that gets you anywhere (it doesn't move any data around). If not, then you have to do it without TRUNCATEONLY. – Tibor Karaszi Jun 11 '19 at 6:55
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.

Improve this answer
4
  • Scott Hodgins brought up compression which is an excellent point, and which reminds me: well-chosen data types can take up less space. If you have columns of BIGINT where INT would do, for example, that'll shave four bytes per field, per record. VARDECIMAL can help a little too. – Jon of All Trades Jun 10 '19 at 17:19
  • This server is not a production server,so can i just take a backup of the database and then delete the original database and restore it again with less initial size.? – user9516827 Jun 10 '19 at 17:28
  • You could take a backup, restore it somewhere else, make deletions, back it up again, and restore it again on the original server. I don't know why, though; you'd have to make the original server read-only during this process, whereas it could remain online if you delete and defrag in situ. – Jon of All Trades Jun 10 '19 at 20:55
  • 1
    Just to make it clear, RESTORE will create the database with the same size as it had when BACKUP was performed. So backup/restore in itself isn't a way to reduce size if a database. – Tibor Karaszi Jun 11 '19 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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